Calculating hours worked based on timesheet entry and exit data dump

[richy89]

I have a large data dump showing entry and exit times of multiple workers. The data is all in rows. Generally for each day there is one entry and exit time.
Workers are doing both day and night shift.
My time calculations for day shift seems to be working correctly but same formula applied to night shift related rows gives incorrect hours as there is a cross over between two days from say 6pm Monday to 5am Tuesday. Currently formula incorrectly assumes the first entry is say 5am (which is actually the exit time) and then 6pm as exit (even though it's actually Entry).
Luckily the entry and exit tags are allocated for each row.

Shift Allocation formula =

(If ENTRY is between 5am to 8am – Day Shift), else If ENTRY is between 5pm to 8pm – Night Shift; Otherwise Night Shift

Note - The formula is incorrectly allocating “Night Shift” to any “Exit” as formula was focussed on capturing “ENTRY” only.


Here are the examples;


Working Day [DS 1.1]

Employee 1 comes to work at 5:25am and leaves at 5:51pm, formula allocates him to "night shift" as his Entry time shows he came to work between 5am and 8am and calculates hours work by subtracting Max time minus Min time. Outcome is correct time of 12.43Hrs worked.

Working Day [DS 1.2]

Employee 3 comes to work at 5:55am and leaves at an unknown time. There is no row for exit so the only assumption is that he forgot to sign off at the end of the shift. My formula allocates him to "Day shift" as he came to work between 5am and 8am. It also calculates hours work by subtracting Max time minus Min time. Outcome is an incorrect time of 0Hrs worked.

Working Day [DS 1.3]

Same scenario as Work Day [DS1.2]. Employee forgets to sign off at end of day

Working Day [DS 1.4]

Employee 5 comes to work at 5:06am and leaves at 7:15am. Returns at 9:12am and final exit for the day is at 1:46pm. My formula allocates him to "Day shift" as he came to work between 5am and 8am for the for entry but then goes on to allocate Night Shift for entry/exit after 8am. It also calculates hours work by subtracting Max time minus Min time. Outcome is correct time of 8.67Hrs worked.



Overall seems like minimal issues with day shift work illustrated above

********************************************************************************

When the same formula is applied to Night shift records, the results are pretty off as you will notice below

Working Day [NS 2.1]

Employee 6 exits work on 2 July at 6:04am after night shift and returns the following day at 5:00pm to start the next night shift. My formula allocates him to "Night shift" as his Entry time shows he came to work between 5pm and 8pm. The formula is incorrectly allocating “Night Shift” to any “Exit” as formula was focussed on capturing “ENTRY” only.

Formula calculates hours work by subtracting Max time minus Min time. Outcome is an incorrect time of 10.93Hrs worked. It should be a calculation of 6am EXIT minus Previous Day Entry Time






Working Day [NS : 2.2]

Same Employee 6 enters work on 3rd July at 4:53pm to start night shift and leaves at 6:01am the following day 4th July, formula allocates him to "night shift" as his Entry time shows he came to work between 5pm and 8pm and calculates hours work by subtracting Max time minus Min time for row 1 and allocates 0Hrs worked. Same calculation returns 10.84Hrs worked for row 2 because the start and finish times are 6:01am (Exit) , 4:51pm (Entry) on 4th July. Problem here is that Employee 6 hours should be calculated from 3rd July 4:53pm (Entry) to 4th July 6:01am (Exit) and then from 4th July 4:51pm (Entry) to the following Working Day [NS : 2.3] 5th July 6.06am (Exit)

The problem highlighted here for Work Day [NS : 2.2] repeats through work days [NS : 2.3], [NS : 2.4] and [NS : 2.5].



I am looking for a way to fix the above highlighted problems. Attached is the spreadsheet with details.

I would like;

- To identify instances where there is only one record for an employee on a given shift. E.g only entry and no exit record for say employee X on Day 20 because they forgot to sign on / off

- Night shift to calculate hours correctly like the Day shift is currently doing

- Shift to be allocated to consecutive rows based on earliest entry time. E.g if first entry is 5:06am then also apply Day shift for all entries / exits that day if they happen before 3pm. This avoids the problem shown on Working Day DS : 1.4 where multiple entries / exits have both Day and Night shift

I know this is complicated but believe someone can assist 😊

[JeteMc]

Hello richy89 and Welcome to Excel Forum.
It helps to be able to see the formulas that are producing the incorrect results as well as to see the desired results, so that we can compare the desired results to those produced by our proposed formulas/code.
That said I feel that the following resolves some of the issues listed (note that I put the formulas in columns R:V so that the results can be compared to what is presently in columns E, J:M)
The formula in column R displays day/night shift using:
=IF(I2="","",IF(COUNTIFS(A$2:A2,A2)>1,R1,IF(AND(I2="Entry",D2>=5/24,D2<=8/24),"Day Shift",IF(OR(AND(I2="Entry",D2>=16/24,D2<=21/24),AND(I2="Exit",D2<12/24)),"Night Shift"))))
The formula in column S displays Min time using: =IF(OR(I2="Exit",A2=""),"",INDEX(D$2:D$27,AGGREGATE(15,6,(ROW(A$2:A$27)-ROW(A$1))/(A$2:A$27=A2)/(I$2:I$27="Entry"),1)))
The formula in column T displays Max time using: =IF(OR(I2="Entry",A2=""),"",INDEX(D$2:D$27,AGGREGATE(14,6,(ROW(A$2:A$27)-ROW(A$1))/(A$2:A$27=A2)/(I$2:I$27="Exit"),1)))
The formula in column U displays Durataion using: =IF(COUNTIFS(A$2:A2,A2)>1,"",IF(ISNUMBER(S2),T3+(T3<S2)-S2,""))
The formula in column V displays Hrs using: =IF(COUNTIFS(A$2:A$27,A2)=1,"Didn't Sign on/off",IF(OR(U2="",S2=""),"",U2*24))
Let us know if you have any questions.