dategoogle-sheetslambdatimeduration

Google sheets - Calculating shift hours

The goal of this spreadsheet is to calculate total hours worked and out of that figure out how many hours has been in the day shift (from 6:00 to 22:00) and how many hours has been in the night shift (from 22:00 to 6:00).

The start and end hours are arbitrary and the duration is also arbitrary, but no more than 24 hours.

Now, the question is if there is any better way of solving this. I went and done each and every condition to come up with the IFS formula.

The formula works, but I am sure that there is a better way.

Also, I want to share this to others that wind up here.

Now, the main column is the Night shift (E) since it has the formula I am questioning right now.

The whole formula:

=IFS(NOT(ISNUMBER(G3)),"",AND(D3>C3,D3-TIME(0,1,0)<TIME(22,0,0),D3+TIME(0,1,0)>TIME(6,0,0),C3-TIME(0,1,0)<TIME(22,0,0),C3+TIME(0,1,0)>TIME(6,0,0)),0,AND(NOT(ISBLANK(D3)),NOT(ISBLANK(C3)),D3=C3),0,C3>D3,IF(D3>TIME(6,0,0),TIMETOINTH(TIME(6,0,0),IF(C3>TIME(22,0,0),C3,TIME(22,0,0))),TIMETOINTH(D3,IF(C3>TIME(22,0,0),C3,TIME(22,0,0)))),AND(D3<=TIME(6,0,0),D3-TIME(0,1,0)<TIME(22,0,0),C3+TIME(0,1,0)>TIME(6,0,0),C3-TIME(0,1,0)<TIME(22,0,0)),TIMETOINTH(D3,TIME(22,0,0)),AND(D3>=TIME(22,0,0),D3+TIME(0,1,0)>TIME(6,0,0),C3+TIME(0,1,0)>TIME(6,0,0),C3-TIME(0,1,0)<TIME(22,0,0)),TIMETOINTH(D3,TIME(22,0,0)),AND(C3<=TIME(6,0,0),C3-TIME(0,1,0)<TIME(22,0,0),D3+TIME(0,1,0)>TIME(6,0,0),D3-TIME(0,1,0)<TIME(22,0,0)),TIMETOINTH(TIME(6,0,0),C3),AND(C3>=TIME(22,0,0),C3>TIME(6,0,0),D3+TIME(0,1,0)>TIME(6,0,0),D3-TIME(0,1,0)<TIME(22,0,0)),TIMETOINTH(TIME(6,0,0),C3))

The same formula with my comments for easier read:

=IFS(
// 1st condition to check if there is something to calculate
NOT(ISNUMBER(G3)),"",
// 2nd condition
AND(
D3>C3,
D3-TIME(0,1,0)<TIME(22,0,0),
D3+TIME(0,1,0)>TIME(6,0,0),
C3-TIME(0,1,0)<TIME(22,0,0),
C3+TIME(0,1,0)>TIME(6,0,0)
),
0, // result for night shift hours
// 3rd condition
AND(
NOT(ISBLANK(D3)),NOT(ISBLANK(C3)),D3=C3
),
0, // result
// 4th condition
C3>D3,
IF(
D3>TIME(6,0,0),
TIMETOINTH(TIME(6,0,0),IF(C3>TIME(22,0,0),C3,TIME(22,0,0))), // result 4.1
TIMETOINTH(D3,IF(C3>TIME(22,0,0),C3,TIME(22,0,0)))), // result 4.2
// fifth condition, 4 subconditions
// 5.1
AND(
D3<=TIME(6,0,0),
D3-TIME(0,1,0)<TIME(22,0,0),
C3+TIME(0,1,0)>TIME(6,0,0),
C3-TIME(0,1,0)<TIME(22,0,0)),
TIMETOINTH(D3,TIME(22,0,0)), // 5.1 result
AND(
D3>=TIME(22,0,0),
D3+TIME(0,1,0)>TIME(6,0,0),
C3+TIME(0,1,0)>TIME(6,0,0),
C3-TIME(0,1,0)<TIME(22,0,0)),
TIMETOINTH(D3,TIME(22,0,0)), // 5.2 result
AND(
C3<=TIME(6,0,0),
C3-TIME(0,1,0)<TIME(22,0,0),
D3+TIME(0,1,0)>TIME(6,0,0),
D3-TIME(0,1,0)<TIME(22,0,0)),
TIMETOINTH(TIME(6,0,0),C3), // 5.3 result
AND(
C3>=TIME(22,0,0),
C3>TIME(6,0,0),
D3+TIME(0,1,0)>TIME(6,0,0),
D3-TIME(0,1,0)<TIME(22,0,0)),
TIMETOINTH(TIME(6,0,0),C3)) //5.4 result

The TIMETOINTH is a named function that translates to =INT(TEXT(end_hour-start_hour,"h")) where both variables must be in the TIME(0,0,0) format.

The inputs are the month/year in the H1 (month/year) and the hours in the C and D columns (6:00 | 06:00 | 23:00).

Thank you.

Solution

  • based on: https://stackoverflow.com/a/78536206/5632629

    F3:

    =INDEX(IF(D3:D33="",,LET(x, 86400, b, C3:C33, c, D3:D33, 
 bb, b*x, cc, c*x, cd, cc+((c<b)*x), 
 IF((bb>("22:00"*x))*(cd>cc)*(cc>("06:00"*x)), 
   MAP(cc, LAMBDA(cc, MIN(cc-("06:00"*x), (("22:00"-"06:00")*x)))), 
   MAP(BYROW(cd, LAMBDA(cd, MIN(cd, ("22:00"*x)))), 
       BYROW(bb, LAMBDA(bb, MAX(bb, ("06:00"*x)))), 
       LAMBDA(x, y, MAX(x-y, 0)))))/60^2))

    enter image description here

    G3:

    =INDEX(LET(s, 86400, c, C3:C33, d, D3:D33, 
 IF((c="")+(d=""),,((IF(d<c, 1)+d)*s-c*s)/60^2)))

    enter image description here

    E3:

    =INDEX(LET(g, G3:G33, f, F3:F33, IF(g<>"", g-f, )))

    enter image description here