Forum Discussion
Arnaldo_Longart
Jul 06, 2024Copper Contributor
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...
- Jul 09, 2024
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.
PeterBartholomew1
Jul 11, 2024Silver Contributor
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.