Calculating day shift and night shift hours from date/time

[BenR]

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?

 

[Bill Rockenbach]

Excel Workbook
	A	B	C	D	E	F
1		From		To
2		Date	Time	Date	Time	Hours:Minutes
3	21/12/09 07:00 to 21/12/09 11:09	21/12/09	7:00	21/12/09	11:09	4:09
4	21/12/09 07:46 to 21/12/09 14:41	21/12/09	7:46	21/12/09	14:41	6:55
5	21/12/09 12:13 to 21/12/09 22:08	21/12/09	12:13	21/12/09	22:08	9:55
6	21/12/09 16:40 to 21/12/09 18:05	21/12/09	16:40	21/12/09	18:05	1:25
7	21/12/09 19:40 to 22/12/09 02:34	21/12/09	19:40	22/12/09	2:34	6:54
8	21/12/09 23:20 to 22/12/09 04:39	21/12/09	23:20	22/12/09	4:39	5:19
9	22/12/09 02:06 to 22/12/09 06:15	22/12/09	2:06	22/12/09	6:15	4:09
Sheet1

 

[BenR]

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

 

[Bill Rockenbach]

when does dayshift end and nightshift start

 

[Bill Rockenbach]

Date formate is MM/DD/YY but formulas will work with your date formate DD/MM/YY

Excel Workbook
	A	B	C	D	E
1
2		From	To	Day Shift	Night Shift *
3	21/12/09 07:00 to 21/12/09 11:09	12/21/09 7:00	12/21/09 11:09	4:09
4	21/12/09 07:46 to 21/12/09 14:41	12/21/09 7:46	12/21/09 14:41	6:55
5	21/12/09 19:40 to 22/12/09 02:34	12/21/09 19:40	12/22/09 2:34		6:54
6	21/12/09 23:20 to 22/12/09 04:39	12/21/09 23:20	12/22/09 4:39		5:19
7	22/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

 

[Bill Rockenbach]

Correction. Assume night shift starts at 7:00 PM

Excel Workbook
	A	B	C	D	E
1
2		From	To	Day Shift	Night Shift *
3	21/12/09 07:00 to 21/12/09 11:09	12/21/09 7:00	12/21/09 11:09	4:09
4	21/12/09 07:46 to 21/12/09 14:41	12/21/09 7:46	12/21/09 14:41	6:55
5	21/12/09 19:40 to 22/12/09 02:34	12/21/09 19:40	12/22/09 2:34		6:54
6	21/12/09 23:20 to 22/12/09 04:39	12/21/09 23:20	12/22/09 4:39		5:19
7	22/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

 

[BenR]

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?

 

[Bill Rockenbach]

I gave up on a formula in favor of UDF's

Excel Workbook
	A	B	C	D	E
1
2		From	To	Day Shift	Night Shift
3	21/12/09 07:00 to 21/12/09 11:09	12/21/09 7:00	12/21/09 11:09	4:09	0:00
4	21/12/09 07:46 to 21/12/09 14:41	12/21/09 7:46	12/21/09 14:41	6:55	0:00
5	21/12/09 12:13 to 21/12/09 22:08	12/21/09 12:13	12/21/09 22:08	6:17	3:38
6	21/12/09 16:40 to 21/12/09 18:05	12/21/09 16:40	12/21/09 18:05	1:25	0:00
7	21/12/09 19:40 to 22/12/09 02:34	12/21/09 19:40	12/22/09 2:34	0:00	6:54
8	21/12/09 23:20 to 22/12/09 04:39	12/21/09 23:20	12/22/09 4:39	0:00	5:19
9	22/12/09 02:06 to 22/12/09 06:15	12/22/09 2:06	12/22/09 6:15	0:00	4:09
Sheet1

A bit messy, but it works. COpy to standard module

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

 

[BenR]

Your the man Bill,

Many thanks.

 

[Michael Avidan]

http://jpg.co.il/download/53a5c26bd5dba.png

Although being 4.5 years late - it seems as if 2 formulas, in the above link, be for any help.

Michael (Micky) Avidan,
Excel" MVP