Hours Worked During Day Shift / Night Shift & Overtime Earned During Day Shift / Night Shift

[garbledmess]

I have browsed these forums looking for an answer, and nothing is exactly what I'm looking for. Hopefully I'm just not seeing and one of you can help me out.

Constraints:
Regular Work Day = 8 hours
Day Shift: 06:00 - 18:00
Night Shift: 18:00 - 06:00

Over Time: Anything past 8 hrs
Day Overtime: 06:00 - 18:00
Night Overtime: 18:00 - 06:00

I need way to calculate the number of Day Hours and Night Hours worked.
AND... the total amount one person can earn of both Day and Night is no more than 8, so, I also need a way to calculate the number Day OT hours and Night OT hours worked over the 8 hour "work day".

All of this is based from a time scheduled and the start time can be variable.
In the example I provided I kept it "easy" starting at midnight for example 1, but we can easily start at at any time and end x number of hours later.

Example 1 (see picture):
Start Time: 00:00 End Time: 11:00

Answer should be:
00:00 – 06:00 = 6 hrs Night (Regular)
06:00 – 08:00 = 2 hrs Day (Regular)
08:00 – 11:00 = 3 hrs Day (Overtime)
Nothing = 0 hrs Night (Overtime)

Example 2 (no picture):
Start Time 17:00 End Time: 06:00

Answer should be:
17:00 – 18:00 = 1 hrs Day (Regular)
18:00 – 01:00 = 7 hrs Night (Regular)
Nothing = 0 hrs Day (Overtime)
01:00 - 06:00 = 5 hrs Night (Overtime)

ANY help is very much appreciated, so thanks in advance!
This is closest thread I've seen come to a solution: Calculate evening- and night-shift hours from working time.

 

[EXCEL MAX]

Hello Garbledmess, format cells (A1 & B1) "Start date" and "End date" as date or time.
Insert this formula.
It should subtract two time values and 8 hours as regular work day time.
The formula is extended with days calculation but you can modify it. Hope this will works for you.
=DAY((B1-A1)-8/24) & " days " & HOUR((B1-A1)-8/24) & " hours " & MINUTE((B1-A1)-8/24) & " minutes "

 

[garbledmess]

Thanks for the quick reply, this does seem close. But I need it do a little more...
I need to first find the split from the regular 8 hours and then the split from the overtime worked; again the start time and the hours worked are variable.
But the count will begin at the start time until 8 hours are reached and then the OT begins. The difference in pay is day vs night.

Example 3:
So say someone starts at 20:00 and works until 07:00 (11 hrs total time)
The answer should look something like:

Day (Regular)	0	Day Overtime	1
Night (Regular)	8	Night Overtime	2

Night Regular: 20:00 - 04:00
Day Regular: 0 (because this person has already worked 8 regular hours)
Night OT: 04:00 - 06:00
Day OT: 06:00 - 07:00

Example 4:
Start: 15:00 End: 04:00 (13 hrs total time)

Day (Regular)	3	Day Overtime	0
Night (Regular)	5	Night Overtime	5

Day Regular:15:00 - 18:00
Night Regular: 18:00 - 23:00
Night OT: 23:00 - 04:00
Day OT: 0 (because this person stopped working at 4am)

 

[Sulprobil]

I suggest to apply this approach:

Calculate Response time using different working hours during the week and excluding weekends / bankholidays

Hello I need a formula where it calculates Response Time considering business hours Mon - Thu 9am till 9pm and Fri 9am-5pm. In addition Bankholidays and Weekends should be excluded. Ticket Received Date is shown in Column B , Call Date is shown in Column C. Formula for Response Time for...

www.mrexcel.com

Overtime would just be the difference of total time between time points minus the scheduled shift time.