Forum Discussion
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 below):
- Time In
- Time out
- Hours worked
- Day Shift (6 am - 6 pm)
- Evening shift (6 pm - 12 am)
- Overnight (12 am - 6 am)
Thanks in advance,
Arnaldo
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.
This question is cross-posted on Chandoo
The solution I offered on that forum was
= LET( adjOut, timeOut+(timeIn>timeOut), allocatedϑ, MAP(timeIn, adjOut, LAMBDA(in,out, LAMBDA( SORT(HSTACK(out,shiftChanges),,,TRUE) - SORT(HSTACK(in,shiftChanges),,,TRUE) ) )), firstNight, MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,1))), firstDay, MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,2))), evening, MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,3))), secondNight, MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,4))), secondDay, MAP(allocatedϑ, LAMBDA(ϑ, INDEX(ϑ(),1,5))), HSTACK(firstDay+secondDay, evening, firstNight+secondNight) )Irrespective of that, since you have accepted a solution here, do not forget that you are obliged to report the fact on the other forum so that contributors do not spend time on a solved problem.
To efficiently distribute hours worked into day shift, evening shift, and overnight shift in Excel, you can use a combination of formulas. Here's a step-by-step guide on how to achieve this:
Step 1: Set Up Your Spreadsheet
Ensure your spreadsheet has the following columns:
- Time In
- Time Out
- Hours Worked
- Day Shift (6 am - 6 pm)
- Evening Shift (6 pm - 12 am)
- Overnight Shift (12 am - 6 am)
Step 2: Calculate Total Hours Worked
Assuming Time In is in column A and Time Out is in column B:
=IF(B2<A2, (B2+1)-A2, B2-A2)
This formula accounts for the possibility that the time out might be after midnight.
Step 3: Calculate Hours for Each Shift
Day Shift
=MAX(0, MIN(B2, TIME(18,0,0)) - MAX(A2, TIME(6,0,0)))
Evening Shift
=MAX(0, MIN(B2, TIME(24,0,0)) - MAX(A2, TIME(18,0,0)))
Overnight Shift
To account for the overnight shift crossing midnight, you need two parts:
- From midnight to the end of the shift:
=MAX(0, MIN(B2, TIME(6,0,0)) - TIME(0,0,0))
- From the beginning of the shift to midnight:
=MAX(0, TIME(6,0,0) - MAX(A2, TIME(0,0,0)))
Combine these two parts into one formula:
=MAX(0, MIN(B2, TIME(6,0,0)) - TIME(0,0,0)) + MAX(0, TIME(6,0,0) - MAX(A2, TIME(0,0,0)))
Step 4: Implement Formulas in Your Spreadsheet
Assuming you start from row 2:
- In column D (Hours Worked):
=IF(B2<A2, (B2+1)-A2, B2-A2)
- In column E (Day Shift):
=MAX(0, MIN(B2, TIME(18,0,0)) - MAX(A2, TIME(6,0,0)))
- In column F (Evening Shift):
=MAX(0, MIN(B2, TIME(24,0,0)) - MAX(A2, TIME(18,0,0)))
- In column G (Overnight Shift):
=MAX(0, MIN(B2, TIME(6,0,0)) - TIME(0,0,0)) + MAX(0, TIME(6,0,0) - MAX(A2, TIME(0,0,0)))
Step 5: Check for Errors
Ensure that the formulas are correctly handling cases where the Time Out is past midnight or spans multiple shifts.
Example
Time In
Time Out
Hours Worked
Day Shift
Evening Shift
Overnight Shift
7:00
15:00
8.0
8.0
0.0
0.0
17:00
23:00
6.0
1.0
5.0
0.0
22:00
7:00
9.0
0.0
2.0
7.0
These formulas should help you accurately distribute the hours worked into the corresponding shifts. The text, steps and formulas was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
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.
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 SnowMan55
Thanks for the file with the correct formulas. It solved the situation; the worked hours are distributed correctly into each shift.
Now, what do you suggest for me to comprehend the formulas you used? I would like to learn about it. My knowledge in MS Excel is limited.
Thanks again, really appreciated
Arnaldo
Start by reading the article on the LET function (I listed the URL in the _Info worksheet). You'll learn that it contains user-assigned names paired with cell references/calculations. The last argument-which is not paired-is the calculation that the function returns.
At its core, the last calculation in each LET function subtracts:- the later (MAX) of the shift start time and the check in time
- from
- the earlier (MIN) of the shift end time and the check out time (the latter of which may have been adjusted up by one day).
The surrounding MAX function uses the larger of zero or the result of that subtraction. (If the times were entered incorrectly, the subtraction might create a negative number, which is not a valid time interval. This function replaces such a negative number with zero.)
The calculation for the overnight shift includes adding the result of an IF function because the employee may have checked in before midnight, and worked into the overnight shift (therefore, again, check in time is a later time than check out time). (Technically, that IF function should have subtracted shift_start from the result of the MIN function, but as shift_start in this case is midnight (=zero), it would be a wasted subtraction anyway.) Peter, Charles, Kate, Gary, Leonard, Paula, Thomas, and William are examples.
Similarly, the calculation for the "ordinary hours" includes adding the result of an IF function because the employee may have checked in before midnight, and worked all the way into the "ordinary hours" shift (therefore, check in time is a later time than check out time). Peter and Charles are examples.
So you can see, storing just times (rather than date-times) complicates the logic needed in the formulas. And if any employee were to work more than 24 hours straight, storing just the times means the formulas cannot detect and handle that situation correctly.
