1
I am trying to sum up hours for an employee worked in one shift. The employees I'm having a problem with are on night shift. The employees entries in excel are shown as individual tickets for work done. Let's say the employee starts a ticket at 11:00pm and ends it at 2:30am, then the next ticket starts at 2:30am and ends at 5:00am. I need those 2 tickets to show entered hours for the date of the first ticket of the shift rather than the next day. I need some form of formula that could work across multiple employees for a month of work on 2 different shifts: Day 5:45am to 6:00pm and Night 5:45pm to 6:00am.
My columns are:
- A) Ticket Number
- B) Employee Name
- C) Total Ticket Hours [4.5]
- D) Start Time [4/5/2018 22:00]
- E) End Time [4/6/2018 2:30]
I am using Office 365 Excel 2016
easttexas937
Posted 2018-08-06T22:06:39.660
Reputation: 21
just subtract D from E and format the result as time. This works if the date/time are true date/time and not strings that work like date time. Also they need to include the date not just the time. – Scott Craner – 2018-08-06T22:24:36.517
Are times stored as Excel date-time or text? Have you tried simply subtracting? What do you get? – fixer1234 – 2018-08-07T04:27:32.337
First apply
mm/dd/yy h:mm AM/PMformat toCol D & Ethen use this formula inCol F=TEXT(E2-D2,"[h]:mm"), using your values with OP you get4:30(4 Hrs & 30 Mnts). – Rajesh S – 2018-08-07T07:40:10.310Always enter Time in
24 Hrs formatlike for5:45 PMshould17:45. – Rajesh S – 2018-08-07T07:49:40.043@ScottCraner The total hours are already calculated in column C. fixer1234 and Rajesh the Date/Time format comes in as "Custom m/d/yyyy h:mm" What I'm trying to get to here is a way to compare a payroll summary that has the employees date/hours worked (on the day that they clocked-in) vs tickets of work done. I need to get those tickets that the driver did after midnight back on the previous day so it will show as accumulated hours on the date of clock-in. – easttexas937 – 2018-08-07T14:01:46.040
After some experimenting, I think I found a way to get my result. I will share it with the group in case someone else falls into this loop. First thing I done was insert a column and copy the column D) Start Time data into the new column. I then changed the format of the new column to General (this broke the time and date down to decimal serial numbers) I then made another new column and came up with the following formula =[@Column2]-0.229 what this does is takes away 5 hours and 29 minutes (0.229) from the start time which in turn throws the ticket back to the original clock-in date. – easttexas937 – 2018-08-07T15:07:44.910
@easttexas937 you may share the answer in the Answer tab, to remove this question from 'unanswered' list. ( : – p._phidot_ – 2018-08-07T18:34:35.870
>