Overtime Formula (1 Viewer)

[BowDan]

Hello, I need urgent help on this matter.

The formula in L works correctly and up to a point, except when the hours change between Shift Length D8 to 10:00, and in Hours Worked G8 shows 11:00, but in Scheduled overtime L8 it shows 01:12 instead of 02:12

=MAX(0, IF(D3>=TIME(10,0,0), IF(D3>H1-TIME(8,48,0)>=TIME(1,0,0),G3-TIME(8,48,0),0), IF(G3>H1-TIME(8,48,0)>=TIME(1,0,0),G3-TIME(8,48,0),0)))

There is times I work longer than my Shift length

I work different Shift length 08:48, 10:00, 11:00 and 12:00.

Overtime is after 8:48

The formula I try to get in Column L: is about which hours is more between Shift Length D and Hours Worked G in Scheduled overtime L

If Shift length is more than Hours worked, Shift length D is 12:00 and Hours worked G is 09:00, If G is less than D = Scheduled overtime L must show 03:12

If Shift length is less than Hours worked, it must show Shift length D is 10:00 and Hours worked G is 11:00. Scheduled overtime L

must show 02:12

Shift length D20 is 10:00 and Hours worked G20 is 13:00. If G is more than D = Scheduled overtime L20

must show 04:12 and not 01:12

Shift length D22 is 12:00 and Hours worked G22 is 13:00. If G is more than D = Scheduled overtime L22

must show 4:12 and not 03:12

 

[CJ_London]

Misread the question

 

[arnelgp]

what i can only see is that you are getting whichever value is "greater" from your computation,

=MAX(0, IF(D9>=TIME(10,0,0), IF(D9>H1-TIME(8,48,0)>=TIME(1,0,0),D9-TIME(8,48,0),0), IF(G9>H1-TIME(8,48,0)>=TIME(1,0,0),G9-TIME(8,48,0),0)))

and the value from column H:

therefore just compare the value of your formula to that of the value in H:
the new formula in column L:

=IF(H3>MAX(0, IF(D3>=TIME(10,0,0), IF(D3>$H$1-TIME(8,48,0)>=TIME(1,0,0),D3-TIME(8,48,0),0), IF(G3>$H$1-TIME(8,48,0)>=TIME(1,0,0),G3-TIME(8,48,0),0))), H3, MAX(0, IF(D3>=TIME(10,0,0), IF(D3>$H$1-TIME(8,48,0)>=TIME(1,0,0),D3-TIME(8,48,0),0), IF(G3>$H$1-TIME(8,48,0)>=TIME(1,0,0),G3-TIME(8,48,0),0))))

 

[BowDan]

what i can only see is that you are getting whichever value is "greater" from your computation,

=MAX(0, IF(D9>=TIME(10,0,0), IF(D9>H1-TIME(8,48,0)>=TIME(1,0,0),D9-TIME(8,48,0),0), IF(G9>H1-TIME(8,48,0)>=TIME(1,0,0),G9-TIME(8,48,0),0)))

and the value from column H:

therefore just compare the value of your formula to that of the value in H:
the new formula in column L:

=IF(H3>MAX(0, IF(D3>=TIME(10,0,0), IF(D3>$H$1-TIME(8,48,0)>=TIME(1,0,0),D3-TIME(8,48,0),0), IF(G3>$H$1-TIME(8,48,0)>=TIME(1,0,0),G3-TIME(8,48,0),0))), H3, MAX(0, IF(D3>=TIME(10,0,0), IF(D3>$H$1-TIME(8,48,0)>=TIME(1,0,0),D3-TIME(8,48,0),0), IF(G3>$H$1-TIME(8,48,0)>=TIME(1,0,0),G3-TIME(8,48,0),0))))

It worked perfectly. Can't thank you enough!