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.
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;
(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.
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
