Calculating overtime for workers

[smscheung]

I would like to make a formula stating below:
worker finishes work at 18:00,
after 18:25, we give half an hour OT
after 18:45, we give 1 hour OT
I also need to take into account some workers might work a few hours OT, therefore if he finishes work 19:25, I need excel to show 1hr30minutes OT
20:45 will be 2 hours and so on.
Is this possible?
Thanks a lot.

 

[etaf]

do they work past midnight

=IF(C5 < TIMEVALUE("18:30"),0,IF(C5 < TIMEVALUE("18:45"),TIME(0,30,0),IF(C5 < = TIMEVALUE("19:00"),TIME(1,0,0),C5-TIMEVALUE("18:45"))))

this assumes C5 is the cell with the time work is finished
also that they do NOT work past midnight
Also i have used less that the times given
so if they finish at exactly 18:25 , thats 30 mins and exactly at 18:45 thats 1hr
if thats not the case change the < to < =

=IF(C5 < = TIMEVALUE("18:30"),0,IF(C5 < = TIMEVALUE("18:45"),TIME(0,30,0),IF(C5 < = TIMEVALUE("19:00"),TIME(1,0,0),C5-TIMEVALUE("18:45"))))

 

[etaf]

i'm confused now
after 18:45 , they get 1 hour
so at 19:00 they still get 1 hour

but at 20:45 , they only get 2 hours
which means from 18:00 to 18:45 - they are NOT paid

 

[Tetra201]

With the end-of-work time in cell A1, the following formulas will give you:

decimal OT hours (formatted as General) =HOUR(MOD(A1-"18:00",1))+LOOKUP(MINUTE(MOD(A1-"18:00",1)),{0,25,45},{0,0.5,1})

OT in hours:minutes (formatted as h:mm) =(HOUR(MOD(A1-"18:00",1))+LOOKUP(MINUTE(MOD(A1-"18:00",1)),{0,25,45},{0,0.5,1}))/24

 

[smscheung]

18:25 - 18:44, they get paid half an hour
Some of them do work past midnight

 

[smscheung]

With the end-of-work time in cell A1, the following formulas will give you:

decimal OT hours (formatted as General) =HOUR(MOD(A1-"18:00",1))+LOOKUP(MINUTE(MOD(A1-"18:00",1)),{0,25,45},{0,0.5,1})

OT in hours:minutes (formatted as h:mm) =(HOUR(MOD(A1-"18:00",1))+LOOKUP(MINUTE(MOD(A1-"18:00",1)),{0,25,45},{0,0.5,1}))/24

This works! Thanks a lot, however one more thing, when there's no OT, the block shows 0, the formula still gives 6 hours (I think it presume it's 00:00), can I add something that if no value keep it at 0?

 

[Tetra201]

Try

=SIGN(A1)*(HOUR(MOD(A1-"18:00",1))+LOOKUP(MINUTE(MOD(A1-"18:00",1)),{0,25,45},{0,0.5,1}))

 

[smscheung]

Try

=SIGN(A1)*(HOUR(MOD(A1-"18:00",1))+LOOKUP(MINUTE(MOD(A1-"18:00",1)),{0,25,45},{0,0.5,1}))

Thanks, this works again.
One final question, similar to this:
They start work at 8am
If they came on/after 7:10, they get half hour OT
If they came before 7:10, they get 1 hour OT
I tried to use the same formula but it doesn't work, I guess this only works forward but not backwards, if you see what I mean.

 

[smscheung]

Thanks, this works again.
One final question, similar to this:
They start work at 8am
If they came on/after 7:10, they get half hour OT
If they came before 7:10, they get 1 hour OT
I tried to use the same formula but it doesn't work, I guess this only works forward but not backwards, if you see what I mean.

Sorry I missed something, If they came in 7:10 - 7:35, they get half an hour, before that will be 1 hour, and similar to before, workers might come in as early as 4:30 in the morning.

 

[Tetra201]

With the start-of-work time in cell A1, the following formulas will give you:

decimal OT hours (formatted as General) =SIGN(A1)*(HOUR("8:00"-A1)+LOOKUP(MINUTE("8:00"-A1),{0,25,51},{0,0.5,1}))

OT in hours:minutes (formatted as h:mm) =SIGN(A1)*(HOUR("8:00"-A1)+LOOKUP(MINUTE("8:00"-A1),{0,25,51},{0,0.5,1}))/24