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

garbledmess

New Member
Joined
Sep 16, 2016
Messages
3
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.
 

Attachments

  • Example1.jpg
    Example1.jpg
    73.5 KB · Views: 11

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Sep 16, 2016
Messages
3
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 Overtime1
Night (Regular)8Night Overtime2

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)3Day Overtime0
Night (Regular)5Night Overtime5


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)
 

Attachments

  • Example4.jpg
    Example4.jpg
    25 KB · Views: 4

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
154
I suggest to apply this approach:
Overtime would just be the difference of total time between time points minus the scheduled shift time.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,029
Messages
5,639,624
Members
417,101
Latest member
amoverton2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top