Determining peak/off-peak durations between time stamps with varied peak hour ranges

[ChevOKeefe]

Hello,

I am reporting on events that can occur at any point and for any given duration. I am given a timestamp for the start of the event, and a timestamp for the end of the event, so can work out the duration easy enough. I need to, however, determine how many hours of this event occurred outwith normal operating hours "Off-peak range", with normal operating hours changing depending on the day. The event can also last several days (60+ hours)

E1 - Time stamp Start Date - DD/MM/YYYY hh:mm:ss
F1 - Time Stamp End Date - DD/MM/YYYY hh:mm:ss
Duration - F1-E1 ((h) mm:ss)

I can define the day (=TEXT(value,"DDD") of each time stamp, and also split time from date using "text to columns", but I'm struggling for a quicker or less manual way than going through every line to determine how many hours of the event occured in the "normal range" and how many in the "off-peak" range.

Normal Hours
Mon-Thur - 07:00 to 21:00
Fri - 07:00 to 19:00
Sat,Sun - 08:00-16:00

an example is below;

Event	Time Stamp Start	Time Stamp End	Duration	Start Day	Hours outside Normal Operation (Off-peak)
1	02/03/2018 01:28 <tbody> </tbody>	04/03/2018 13:49 <tbody> </tbody>	60:20:38 <tbody> </tbody>	Fri
2	12/03/2018 11:37 <tbody> </tbody>	12/03/2018 20:19 <tbody> </tbody>	8:41:21 <tbody> </tbody>	Mon <tbody> </tbody>
3	18/01/2018 03:19 <tbody> </tbody>	18/01/2018 03:48 <tbody> </tbody>	0:29:43 <tbody> </tbody>	Thu
4	19/01/2018 13:49 <tbody> </tbody>	19/01/2018 16:04 <tbody> </tbody>	2:15:16 <tbody> </tbody>	Fri
5	20/01/2018 13:35 <tbody> </tbody> <tbody> </tbody>	21/01/2018 11:00 <tbody> </tbody> <tbody> </tbody>	21:24:26 <tbody> </tbody> <tbody> </tbody>	Sat <tbody> </tbody>
6	08/12/2017 13:39 <tbody> </tbody>	10/12/2017 11:02 <tbody> </tbody>	45:22:59 <tbody> </tbody>	Fri <tbody> </tbody>

<tbody>
</tbody>

Thanks in advance for any help with this.

Chev

 

[Fazza]

Hi, Chev

suggest a code solution. to implement, ALT-F11 from Excel worksheet and in the VBA insert a code module with function code below (please google for extra info on this process if required, or ask again)

within Excel, enter formula like =OffPeak(cell with start time, cell with end time)
format same as duration cells

code doesn't include error handling - which means that so long as the inputs are good, all should be OK. But if inputs are wrong in some way, anything could happen

Function OffPeak(ByVal StartTime As Date, ByVal EndTime As Date) As Date


    'https://www.mrexcel.com/forum/excel-questions/1053216-determining-peak-off-peak-durations-between-time-stamps-varied-peak-hour-ranges.html
    
    'Calculate hours between StartTime & EndTime outside normal hours
    
    'Normal Hours
    'Mon-Thur - 07:00 to 21:00
    'Fri - 07:00 to 19:00
    'Sat,Sun - 08:00-16:00


    Dim StartDay As Date
    Dim EndDay As Date
    Dim loopDay As Date
    
    Dim PeakStartTimeForLoopDay As Date
    Dim PeakEndTimeForLoopDay As Date
    
    Dim EventStartTimeForLoopDay As Date
    Dim EventEndTimeForLoopDay As Date
    
    
    OffPeak = 0
    StartDay = Int(StartTime)
    EndDay = Int(EndTime)
    
    'Loop through each day, accumulating off-peak hours day by day
    For loopDay = StartDay To EndDay
                
        'Daily limits for this loop day
        Select Case Format$(loopDay, "ddd")
            Case "Mon", "Tue", "Wed", "Thu"
                PeakStartTimeForLoopDay = 7 / 24
                PeakEndTimeForLoopDay = 21 / 24
            Case "Fri"
                PeakStartTimeForLoopDay = 7 / 24
                PeakEndTimeForLoopDay = 19 / 24
            Case "Sat", "Sun"
                PeakStartTimeForLoopDay = 8 / 24
                PeakEndTimeForLoopDay = 16 / 24
        End Select
        
        'if the event starts on this loop day
        If StartDay = loopDay Then
            EventStartTimeForLoopDay = StartTime - Int(StartTime)
        Else
            'the event started at an earlier day, so begins at midnight (zero hours) on this loop day
            EventStartTimeForLoopDay = 0
        End If
        
        'if the event ends on this loop day
        If EndDay = loopDay Then
            EventEndTimeForLoopDay = EndTime - Int(EndTime)
        Else
            'the event ends on a later day, so ends at midnight (24 hours) on this loop day
            EventEndTimeForLoopDay = 1
        End If


        'Accumulate hours on this loop day before this day's peak time
        If EventStartTimeForLoopDay < PeakStartTimeForLoopDay Then OffPeak = OffPeak + PeakStartTimeForLoopDay - EventStartTimeForLoopDay
        'Accumulate hours on this loop day after this day's peak time
        If EventEndTimeForLoopDay > PeakEndTimeForLoopDay Then OffPeak = OffPeak + EventEndTimeForLoopDay - PeakEndTimeForLoopDay
        
    Next loopDay
    
End Function

 

[ChevOKeefe]

Hi, Chev

suggest a code solution. to implement, ALT-F11 from Excel worksheet and in the VBA insert a code module with function code below (please google for extra info on this process if required, or ask again)

within Excel, enter formula like =OffPeak(cell with start time, cell with end time)
format same as duration cells

code doesn't include error handling - which means that so long as the inputs are good, all should be OK. But if inputs are wrong in some way, anything could happen

Function OffPeak(ByVal StartTime As Date, ByVal EndTime As Date) As Date


    'https://www.mrexcel.com/forum/excel-questions/1053216-determining-peak-off-peak-durations-between-time-stamps-varied-peak-hour-ranges.html
    
    'Calculate hours between StartTime & EndTime outside normal hours
    
    'Normal Hours
    'Mon-Thur - 07:00 to 21:00
    'Fri - 07:00 to 19:00
    'Sat,Sun - 08:00-16:00


    Dim StartDay As Date
    Dim EndDay As Date
    Dim loopDay As Date
    
    Dim PeakStartTimeForLoopDay As Date
    Dim PeakEndTimeForLoopDay As Date
    
    Dim EventStartTimeForLoopDay As Date
    Dim EventEndTimeForLoopDay As Date
    
    
    OffPeak = 0
    StartDay = Int(StartTime)
    EndDay = Int(EndTime)
    
    'Loop through each day, accumulating off-peak hours day by day
    For loopDay = StartDay To EndDay
                
        'Daily limits for this loop day
        Select Case Format$(loopDay, "ddd")
            Case "Mon", "Tue", "Wed", "Thu"
                PeakStartTimeForLoopDay = 7 / 24
                PeakEndTimeForLoopDay = 21 / 24
            Case "Fri"
                PeakStartTimeForLoopDay = 7 / 24
                PeakEndTimeForLoopDay = 19 / 24
            Case "Sat", "Sun"
                PeakStartTimeForLoopDay = 8 / 24
                PeakEndTimeForLoopDay = 16 / 24
        End Select
        
        'if the event starts on this loop day
        If StartDay = loopDay Then
            EventStartTimeForLoopDay = StartTime - Int(StartTime)
        Else
            'the event started at an earlier day, so begins at midnight (zero hours) on this loop day
            EventStartTimeForLoopDay = 0
        End If
        
        'if the event ends on this loop day
        If EndDay = loopDay Then
            EventEndTimeForLoopDay = EndTime - Int(EndTime)
        Else
            'the event ends on a later day, so ends at midnight (24 hours) on this loop day
            EventEndTimeForLoopDay = 1
        End If


        'Accumulate hours on this loop day before this day's peak time
        If EventStartTimeForLoopDay < PeakStartTimeForLoopDay Then OffPeak = OffPeak + PeakStartTimeForLoopDay - EventStartTimeForLoopDay
        'Accumulate hours on this loop day after this day's peak time
        If EventEndTimeForLoopDay > PeakEndTimeForLoopDay Then OffPeak = OffPeak + EventEndTimeForLoopDay - PeakEndTimeForLoopDay
        
    Next loopDay
    
End Function

hi Fazza, thanks for this and the quick response, you have no idea how much it's appreciated. Though it appeared to work, sense checking has returned an issue. Below is the data using the code and Offpeak function as per your excellent instruction. The issue appears to be occurring on short duration stops (see bottom 2 rows and row 4 below), where the "off peak" hours is being recorded as a higher value than the stop duration itself. It's almost there!

13/12/2017 11:00	13/12/2017 13:45	2:45:01	Fri	0:00:00
29/12/2017 02:03	29/12/2017 10:58	8:54:54	Tue	4:56:35
09/01/2018 09:17	09/01/2018 09:29	0:12:21	Thu	0:00:00
18/01/2018 03:18	18/01/2018 03:47	0:29:02	Wed	3:41:37
31/01/2018 10:23	31/01/2018 10:23	0:00:32	Wed	0:00:00
31/01/2018 10:25	31/01/2018 14:35	4:10:20	Mon	0:00:00
26/02/2018 11:55	26/02/2018 15:45	3:50:20	Mon	0:00:00
26/02/2018 15:48	26/02/2018 15:57	0:08:58	Fri	0:00:00
02/03/2018 05:09	02/03/2018 11:44	6:34:47	Mon	1:50:25

<tbody>
</tbody>

02/03/2018 01:28

04/03/2018 13:49

60:20:38

Mon

34:31:12

<tbody>
</tbody>

18/12/2017 23:16	18/12/2017 23:21	0:05:01	Mon	2:21:06
20/12/2017 01:01	20/12/2017 01:06	0:05:00	Wed	5:58:21

<tbody>
</tbody>

18/03/2018 21:03

18/03/2018 21:29

0:25:14

Sun

5:29:12

<tbody>
</tbody>

 

[Fazza]

Error in my thinking. This is better,

Function OffPeak(ByVal StartTime As Date, ByVal EndTime As Date) As Date


'==============================================
'   'https://www.mrexcel.com/forum/excel-questions/1053216-determining-peak-off-peak-durations-between-time-stamps-varied-peak-hour-ranges.html
'
'   Calculate hours between StartTime & EndTime outside normal hours.
'   Normal hours are hard-coded into Select Case code,
'       Mon-Thur - 07:00 to 21:00
'       Fri - 07:00 to 19:00
'       Sat,Sun - 08:00-16:00
'
'
'   Date    Change
'   ------------------------------------------------------------------------------
'   26-Apr-18 Created
'   27-Apr-18 Fix up handling of finishing before the peak starts, or starting after it ends.
'
'==============================================
    
    Dim StartDay As Date
    Dim EndDay As Date
    Dim loopDay As Date
    
    Dim PeakStartTimeForLoopDay As Date
    Dim PeakEndTimeForLoopDay As Date
    
    Dim EventStartTimeForLoopDay As Date
    Dim EventEndTimeForLoopDay As Date
    
    
    OffPeak = 0
    StartDay = Int(StartTime)
    EndDay = Int(EndTime)
    
    'Loop through each day, accumulating off-peak hours day by day
    For loopDay = StartDay To EndDay
                
        'Daily limits for this loop day
        Select Case Format$(loopDay, "ddd")
            Case "Mon", "Tue", "Wed", "Thu"
                PeakStartTimeForLoopDay = 7 / 24
                PeakEndTimeForLoopDay = 21 / 24
            Case "Fri"
                PeakStartTimeForLoopDay = 7 / 24
                PeakEndTimeForLoopDay = 19 / 24
            Case "Sat", "Sun"
                PeakStartTimeForLoopDay = 8 / 24
                PeakEndTimeForLoopDay = 16 / 24
        End Select
        
        'if the event starts on this loop day
        If StartDay = loopDay Then
            EventStartTimeForLoopDay = StartTime - Int(StartTime)
        Else
            'the event started at an earlier day, so begins at midnight (zero hours) on this loop day
            EventStartTimeForLoopDay = 0
        End If
        
        'if the event ends on this loop day
        If EndDay = loopDay Then
            EventEndTimeForLoopDay = EndTime - Int(EndTime)
        Else
            'the event ends on a later day, so ends at midnight (24 hours) on this loop day
            EventEndTimeForLoopDay = 1
        End If


        'Accumulate hours on this loop day before this day's peak time
        If EventStartTimeForLoopDay < PeakStartTimeForLoopDay Then
            'There are two possibilities,
            '1. the event finishes before the peak starts,
            If EventEndTimeForLoopDay < PeakStartTimeForLoopDay Then
                OffPeak = OffPeak + EventEndTimeForLoopDay - EventStartTimeForLoopDay
            Else
            '2. the event is ongoing when the peak starts
                OffPeak = OffPeak + PeakStartTimeForLoopDay - EventStartTimeForLoopDay
            End If
        End If
        
        'Accumulate hours on this loop day after this day's peak time
        If EventEndTimeForLoopDay > PeakEndTimeForLoopDay Then
            'There are two possibilities,
            '1. the event starts after the peak ends,
            If EventStartTimeForLoopDay > PeakEndTimeForLoopDay Then
                OffPeak = OffPeak + EventEndTimeForLoopDay - EventStartTimeForLoopDay
            Else
            '2. the event is ongoing when the peak ends
                OffPeak = OffPeak + EventEndTimeForLoopDay - PeakEndTimeForLoopDay
            End If
        End If
        
    Next loopDay
    
End Function