Forum Discussion
Hours worked to be distributed into day shift, evening shift, and overnight shift
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.
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.