Forum Discussion
Solved
Hours worked to be distributed into day shift, evening shift, and overnight shift
Hi ! I need help please. I'm not sure how to efficiently distribute the hours worked by each worker into the different shift. The information in the spreadsheet is as follow (also see screenshot b...
Arnaldo_Longart The following formulas seem to work for the three shifts:
=LET( time_in, $B2, time_out, $C2 + IF($C2 < time_in, 1, 0), shift_start, TIME(6,0,0), shift_end, TIME(18,0,0), MAX( 0, MIN(time_out, shift_end) - MAX(time_in, shift_start) ) + IF( $C2 < time_in, MAX(0, $C2 - shift_start), 0 ) ) =LET( time_in, $B2, time_out, $C2 + IF($C2 < time_in, 1, 0), shift_start, TIME(18,0,0), shift_end, 1, MAX( 0, MIN(time_out, shift_end) - MAX(time_in, shift_start) ) ) =LET( time_in, $B2, time_out, $C2 + IF($C2 < time_in, 1, 0), shift_start, TIME(0,0,0), shift_end, TIME(6,0,0), MAX( 0, MIN(time_out, shift_end) - MAX(time_in, shift_start) ) + IF( $C2 < time_in, MIN($C2, shift_end), 0 ) )(Spaces are included for readability; they are not required.)
The attached workbook includes notes and more test data.
Hi NikolinoDE,
Thanks for the quick reply.
I think you are in the right track, however as I entered the formulas there are still some issues (red font ones). For example, in the case of 8 hours, the formula assigned correctly the 8 hours to the "day shift", however also added 6 hours to the "overnight shift".
I tried to attached the ms excel spreadsheet but didn't allow me. I used the figures from the example you mentioned in your previous emal.
| Time in | Time out | Hours worked | day shift (6 am - 6 pm) | evening shift (6 pm - 12 am) | overnight (12 am - 6 am) |
| 7:00 | 15:00 | 8:00 | 8:00 | 0:00 | 6:00 |
| 17:00 | 23:00 | 6:00 | 1:00 | 0:00 | 6:00 |
| 22:00 | 7:00 | 9:00 | 0:00 | 0:00 | 6:00 |
Please, can you check it to see what would be the issue.
Once again thanks,
Arnaldo
It looks like there may be some issues with the formulas provided, especially when it comes to correctly distributing the hours into the shifts. Let’s rework the formulas to ensure they correctly distribute the hours.
Step-by-Step Breakdown
- Setup Columns:
- A - Time In
- B - Time Out
- C - Hours Worked (total hours)
- D - Day Shift (6 am - 6 pm)
- E - Evening Shift (6 pm - 12 am)
- F - Overnight Shift (12 am - 6 am)
Step 1: Calculate Total Hours Worked
Place the following formula in C2:
=IF(B2<A2, (B2+1)-A2, B2-A2)
This formula calculates the total hours worked, accounting for cases where the time out is after midnight.
Step 2: Calculate Hours for Each Shift
Now, let's define the formulas to accurately split the hours across the shifts.
Day Shift (6 am - 6 pm) Place this formula in D2:
=MAX(0, (MIN(B2, TIME(18,0,0)) - MAX(A2, TIME(6,0,0))) * 24)
Evening Shift (6 pm - 12 am) Place this formula in E2:
=MAX(0, (MIN(B2, TIME(24,0,0)) - MAX(A2, TIME(18,0,0))) * 24)
Overnight Shift (12 am - 6 am) This one needs to handle two parts as shifts can span across midnight.
First part: From midnight to end of shift:
=MAX(0, (MIN(B2, TIME(6,0,0)) - TIME(0,0,0)) * 24)
Second part: From start of shift to midnight:
=MAX(0, (TIME(6,0,0) - MAX(A2, TIME(0,0,0))) * 24)
Combine both parts into one formula in F2:
=MAX(0, (MIN(B2, TIME(6,0,0)) - TIME(0,0,0)) * 24) + MAX(0, (TIME(6,0,0) - MAX(A2, TIME(0,0,0))) * 24)
Step 3: Implement and Test
Using the above formulas, implement them in the corresponding cells.
Example:
Time In | Time Out | Hours Worked | Day Shift | Evening Shift | Overnight Shift |
7:00 | 15:00 | =IF(B2<A2, (B2+1)-A2, B2-A2) | =MAX(0, (MIN(B2, TIME(18,0,0)) - MAX(A2, TIME(6,0,0))) * 24) | =MAX(0, (MIN(B2, TIME(24,0,0)) - MAX(A2, TIME(18,0,0))) * 24) | =MAX(0, (MIN(B2, TIME(6,0,0)) - TIME(0,0,0)) * 24) + MAX(0, (TIME(6,0,0) - MAX(A2, TIME(0,0,0))) * 24) |
17:00 | 23:00 | =IF(B2<A2, (B2+1)-A2, B2-A2) | =MAX(0, (MIN(B2, TIME(18,0,0)) - MAX(A2, TIME(6,0,0))) * 24) | =MAX(0, (MIN(B2, TIME(24,0,0)) - MAX(A2, TIME(18,0,0))) * 24) | =MAX(0, (MIN(B2, TIME(6,0,0)) - TIME(0,0,0)) * 24) + MAX(0, (TIME(6,0,0) - MAX(A2, TIME(0,0,0))) * 24) |
22:00 | 7:00 | =IF(B2<A2, (B2+1)-A2, B2-A2) | =MAX(0, (MIN(B2, TIME(18,0,0)) - MAX(A2, TIME(6,0,0))) * 24) | =MAX(0, (MIN(B2, TIME(24,0,0)) - MAX(A2, TIME(18,0,0))) * 24) | =MAX(0, (MIN(B2, TIME(6,0,0)) - TIME(0,0,0)) * 24) + MAX(0, (TIME(6,0,0) - MAX(A2, TIME(0,0,0))) * 24) |
Verification
Make sure to validate the calculated hours for each shift with different scenarios to ensure they align correctly:
- Shift starting and ending within the same day.
- Shift crossing over from one day to the next.
- Shift spanning across multiple shifts (e.g., starting in the evening and ending in the morning).
These formulas should correctly distribute the hours worked into the respective shifts without overlapping.
Hi,
Thanks, but it is not working.
Here is the screenshot of my spreadsheet after implementing the updates in the formulas.
Please let me know if you any other idea to solve it.
Thanks, Nikolino
Arnaldo
- A file (without sensitive data) as well as information about Excel version, storage medium, etc. would also help.
