Calculating day shift and night shift hours from date/time

B

BenR

New Member
Joined
Jan 4, 2006
Messages
22
I want to find the hours worked in day shift and night shift. Day shift is from 06:30 to 18:30 and night shift is from 18:30 to 06:30. Listed below is an example of my date/times.
Note that the night shift carries over to the next day.

Start/Finish
21/12/09 07:00 to 21/12/09 11:09
21/12/09 07:46 to 21/12/09 14:41
21/12/09 12:13 to 21/12/09 22:08
21/12/09 16:40 to 21/12/09 18:05
21/12/09 19:40 to 22/12/09 02:34
21/12/09 23:20 to 22/12/09 04:39
22/12/09 02:06 to 22/12/09 06:15

any ideas?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Excel Workbook
ABCDEF
1FromTo
2DateTimeDateTimeHours:Minutes
321/12/09 07:00 to 21/12/09 11:0921/12/097:0021/12/0911:094:09
421/12/09 07:46 to 21/12/09 14:4121/12/097:4621/12/0914:416:55
521/12/09 12:13 to 21/12/09 22:0821/12/0912:1321/12/0922:089:55
621/12/09 16:40 to 21/12/09 18:0521/12/0916:4021/12/0918:051:25
721/12/09 19:40 to 22/12/09 02:3421/12/0919:4022/12/092:346:54
821/12/09 23:20 to 22/12/09 04:3921/12/0923:2022/12/094:395:19
922/12/09 02:06 to 22/12/09 06:1522/12/092:0622/12/096:154:09
Sheet1
 
Upvote 0
Thanks for you response, but this does not quite answer my question.

I am looking for two result columns. One with hours worked on day shift and the other with hours worked on night shift.

My initial data has two columns 1) start date/time and 2) finish date/time in the format of dd/mm/yyyy hh:mm
 
Upvote 0
Date formate is MM/DD/YY but formulas will work with your date formate DD/MM/YY
Excel Workbook
ABCDE
1
2FromToDay ShiftNight Shift *
321/12/09 07:00 to 21/12/09 11:0912/21/09 7:0012/21/09 11:094:09 
421/12/09 07:46 to 21/12/09 14:4112/21/09 7:4612/21/09 14:416:55 
521/12/09 19:40 to 22/12/09 02:3412/21/09 19:4012/22/09 2:34 6:54
621/12/09 23:20 to 22/12/09 04:3912/21/09 23:2012/22/09 4:39 5:19
722/12/09 02:06 to 22/12/09 06:15
8
9*Assume night shift starts at 8:00 PM Date format MM/DD/YY
Sheet3
 
Upvote 0
Correction. Assume night shift starts at 7:00 PM
Excel Workbook
ABCDE
1
2FromToDay ShiftNight Shift *
321/12/09 07:00 to 21/12/09 11:0912/21/09 7:0012/21/09 11:094:09 
421/12/09 07:46 to 21/12/09 14:4112/21/09 7:4612/21/09 14:416:55 
521/12/09 19:40 to 22/12/09 02:3412/21/09 19:4012/22/09 2:34 6:54
621/12/09 23:20 to 22/12/09 04:3912/21/09 23:2012/22/09 4:39 5:19
722/12/09 02:06 to 22/12/09 06:15
8
9*Assume night shift starts at 7:00 PM Date format MM/DD/YY
Sheet3
 
Upvote 0
Thanks Bill,

The answer is still not quite there. Day shift is from 06:30 to 18:30 and night shift is from 18:30 to 06:30.

If a shift crosses over day shift and night shift (ie from 14:00 to 22:00) I need to seperate out the hours worked in each shift (ie 4.5 hours in day shift and 3.5 hours in night shift)

Any ideas?
 
Upvote 0
I gave up on a formula in favor of UDF's

Excel Workbook
ABCDE
1
2FromToDay ShiftNight Shift
321/12/09 07:00 to 21/12/09 11:0912/21/09 7:0012/21/09 11:094:090:00
421/12/09 07:46 to 21/12/09 14:4112/21/09 7:4612/21/09 14:416:550:00
521/12/09 12:13 to 21/12/09 22:0812/21/09 12:1312/21/09 22:086:173:38
621/12/09 16:40 to 21/12/09 18:0512/21/09 16:4012/21/09 18:051:250:00
721/12/09 19:40 to 22/12/09 02:3412/21/09 19:4012/22/09 2:340:006:54
821/12/09 23:20 to 22/12/09 04:3912/21/09 23:2012/22/09 4:390:005:19
922/12/09 02:06 to 22/12/09 06:1512/22/09 2:0612/22/09 6:150:004:09
Sheet1


A bit messy, but it works. COpy to standard module
Code: 
Option Explicit
Function dShiftHrs(rngStartTime As Range, rngEndTime As Range) As Double
    Dim StartTime As Double
    Dim EndTime As Double
    Dim ShiftHours
    Const DaytStart As Double = 0.270833333
    Const NightStart As Double = 0.770833334
    StartTime = rngStartTime - Int(rngStartTime)
    EndTime = rngEndTime - Int(rngEndTime)
    If Int(rngStartTime) = Int(rngEndTime) Then    ' all hours in same day
        If StartTime >= NightStart Then   'all night shift do nothing
        ElseIf EndTime < DaytStart Then  'all night shift do nothing
        ElseIf StartTime >= DaytStart And EndTime <= NightStart Then  'all day shift
            dShiftHrs = EndTime - StartTime
        ElseIf StartTime >= DaytStart And EndTime > NightStart Then  'day shift and night shift
            dShiftHrs = NightStart - StartTime  'day shift hours
        ElseIf StartTime < DaytStart And EndTime < NightStart Then    'night shift and day shift
            dShiftHrs = EndTime - DaytStart
        ElseIf StartTime < DaytStart And EndTime > NightStart Then    'Night Shift, DayShift, Night Shift
            dShiftHrs = 0.5
        End If
    Else    'hours in two days
        If StartTime > NightStart And EndTime < DaytStart Then  'all night shift do nothing
        ElseIf StartTime > NightStart And EndTime > DaytStart And EndTime < NightStart Then  'night shift day shift
            dShiftHrs = EndTime - DaytStart
        ElseIf StartTime > NightStart And EndTime > NightStart Then   'night shift, day shift, night shift
            dShiftHrs = 0.5
        ElseIf StartTime < NightStart And StartTime > DaytStart And EndTime < DaytStart Then  'day shift, night shift, night shift
            dShiftHrs = NightStart - StartTime
        ElseIf StartTime < NightStart And StartTime > DaytStart And EndTime < NightStart Then  'day shift, night shift, night shift
            dShiftHrs = NightStart - StartTime + EndTime - DaytStart
        ElseIf StartTime < DaytStart And EndTime > NightStart Then   'night shift, day shift, night shift, night shift,day shift,night shift
            dShiftHrs = 0.99999
        ElseIf StartTime < DaytStart And EndTime > DaytStart And EndTime < NightStart Then    'night shift, day shift, night shift, night shift,day shift
            dShiftHrs = 0.5 + EndTime - DaytStart
        ElseIf StartTime < NightStart And EndTime > NightStart Then   'day shift, night shift, night shift,day shift,night shift
            dShiftHrs = NightStart - StartTime + 0.5
        ElseIf StartTime < DaytStart And EndTime < DaytStart Then   'night shift, day shift, night shift, night shift
            dShiftHrs = 0.5
        End If
    End If
End Function
Function nShiftHrs(rngStartTime As Range, rngEndTime As Range) As Double
    Dim StartTime As Double
    Dim EndTime As Double
    Dim ShiftHours
        Const DaytStart As Double = 0.270833333
    Const NightStart As Double = 0.770833334
    StartTime = rngStartTime - Int(rngStartTime)
    EndTime = rngEndTime - Int(rngEndTime)
    If Int(rngStartTime) = Int(rngEndTime) Then    ' all hours in same day
        If StartTime >= NightStart Then   'all night shift
            nShiftHrs = EndTime - StartTime
        ElseIf EndTime < DaytStart Then  'all night shift
            nShiftHrs = EndTime - StartTime
        ElseIf StartTime >= DaytStart And EndTime <= NightStart Then  'all day shift do nothing
        ElseIf StartTime >= DaytStart And EndTime > NightStart Then  'day shift and night shift
            nShiftHrs = EndTime - NightStart  'night shift hours
        ElseIf StartTime < DaytStart And EndTime < NightStart Then    'night shift and day shift
            nShiftHrs = DaytStart - StartTime
        ElseIf StartTime < DaytStart And EndTime > NightStart Then    'Night Shift, DayShift, Night Shift
            nShiftHrs = DaytStart - StartTime + EndTime - NightStart
        End If
        
    Else    'hours in two days
        If StartTime > NightStart And EndTime < DaytStart Then  'all night shift
            nShiftHrs = 1 - StartTime + EndTime
        ElseIf StartTime > NightStart And EndTime > DaytStart And EndTime < NightStart Then  'night shift day shift
            nShiftHrs = 1 - StartTime + DaytStart
        ElseIf StartTime > NightStart And EndTime > NightStart Then   'night shift, day shift, night shift
            nShiftHrs = 1 - StartTime + DaytStart + EndTime - NightStart
        ElseIf StartTime < NightStart And StartTime > DaytStart And EndTime < DaytStart Then  'day shift, night shift, night shift
            nShiftHrs = 1 - NightStart + EndTime
        ElseIf StartTime < NightStart And StartTime > DaytStart And EndTime < NightStart Then  'day shift, night shift, night shift
            nShiftHrs = 0.5
        ElseIf StartTime < DaytStart And EndTime > NightStart Then   'night shift, day shift, night shift, night shift,day shift,night shift
            nShiftHrs = DaytStart - StartTime + 0.5 + EndTime - NightStart
        ElseIf StartTime < DaytStart And EndTime > DaytStart And EndTime < NightStart Then    'night shift, day shift, night shift, night shift,day shift
            nShiftHrs = DaytStart - StartTime + 0.5
        ElseIf StartTime < NightStart And EndTime > NightStart Then   'day shift, night shift, night shift,day shift,night shift
            nShiftHrs = 1 - NightStart + DaytStart + EndTime - NightStart
        ElseIf StartTime < DaytStart And EndTime < DaytStart Then   'night shift, day shift, night shift, night shift
            nShiftHrs = DaytStart - StartTime + 1 - NightStart + EndTime

        End If
    End If

End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Formula Help.
Replies
3
Views
59
Fluff
Fluff
J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
98
etaf
etaf
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
370
kevin9999
K
J
  • Solved
Add 1 day to a date within a range as long as 2 conditions are met [without macros]
Replies
4
Views
565
juanam
J
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
310
awoohaw
awoohaw

Forum statistics

Threads
1,214,940
Messages
6,122,352
Members
449,080
Latest member
Armadillos

Share this page

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
Back
Top