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
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