Trying to calculate manhours on normal working/overtime and premium overtime

I'm working on the man hours for my workers and need 3 separate hours. Tried a lot of formulas but none does the job. Please help.

Heres the breakdown:

Normal hours : 8am - 5pm (8Hrs - Lunch Break 1Hrs)

Normal OT :5pm - 12am (7Hrs)

Premium OT :12am - 8am (8Hrs)

Saturday from 8am - 12am is consider Normal OT

Sunday from 12am-12am(Monday) is consider Premium OT
Daily Activities AFR.xlsx

trying-to-calculate-manhours-on-normal-working-overtime-and-premium-overtime-daily-activities-a.xlsxformula for column J

Hi vogel997

Thank you for your reply, the formula you gave wont work if I were to input 19:00 - 23:00, column J will return -2.00 for nhr when it suppose to be 0 and 4.00 in N OT Hr.
Here's what I found:

Where:
normal hrs is column J
overtime hrs is column K
premium ot is column L

e.g
08:00 - 14:00 - should be 5 normal hrs
13:00 - 17:00 - should be 4 normal hrs (formula does not identify lunch break hence auto deduct 1hr)
15:00 - 2:00(next day) - should be 2 normal hrs | 7 overtime hrs | 2 premium ot hrs

so there should be formulas for the 3 column but I can't figure it out.

Daily Activities AFR.xlsx
this should cover most of the variables.

Hi vogel997,

Thank you so much for resolving this issue. The only bugs is that when I tried to input Start time 16:00 End Time 2:00(next day) it will end up up as:

nhr - -14.00 vs actual 1.00
ot - 22.00 vs actual 7.00
p ot - 2.00(same as actual)

Daily Activities AFR.xlsx

This should correct the problem.

Hi vogel997,

Now it shows as:

nhr - -8.00 vs actual 1.00
ot - 0.00 vs actual 7.00
p ot - 2.00(same as actual)

Daily Activities AFR I.xlsx

This time it will work