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.

 

[smscheung]

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

Thanks a lot for your help, now all my problems are solved! Also learned this new formula

 

[Tetra201]

You are welcome.