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

C

ChevOKeefe

New Member
Joined
Apr 25, 2018
Messages
4
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;

[TABLE="width: 500"]
<tbody>[TR]
[TD]Event[/TD]
[TD]Time Stamp Start[/TD]
[TD]Time Stamp End[/TD]
[TD]Duration[/TD]
[TD]Start Day[/TD]
[TD]Hours outside Normal Operation (Off-peak)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108, align: right"]02/03/2018 01:28[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]04/03/2018 13:49[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl65, width: 78, align: right"]60:20:38[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Fri[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108, align: right"]12/03/2018 11:37[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/03/2018 20:19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl65, width: 78, align: right"]8:41:21[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Mon[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108, align: right"]18/01/2018 03:19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]18/01/2018 03:48[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl65, width: 78, align: right"]0:29:43[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Thu[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108, align: right"]19/01/2018 13:49[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]19/01/2018 16:04[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl65, width: 78, align: right"]2:15:16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Fri[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108, align: right"][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108, align: right"]20/01/2018 13:35[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]21/01/2018 11:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl65, width: 78, align: right"][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl65, width: 78, align: right"]21:24:26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Sat[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 108"]
<tbody>[TR]
[TD="class: xl65, width: 108, align: right"]08/12/2017 13:39[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]10/12/2017 11:02[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 78"]
<tbody>[TR]
[TD="class: xl65, width: 78, align: right"]45:22:59[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Fri[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance for any help with this.

Chev
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
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 :-)

Code: 
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
 
Upvote 0
Fazza said:
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 :-)

Code: 
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
Click to expand...

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!


[TABLE="width: 439"]
<tbody>[TR]
[TD="align: right"]13/12/2017 11:00[/TD]
[TD="align: right"]13/12/2017 13:45[/TD]
[TD="align: right"]2:45:01[/TD]
[TD]Fri[/TD]
[TD="align: right"]0:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]29/12/2017 02:03[/TD]
[TD="align: right"]29/12/2017 10:58[/TD]
[TD="align: right"]8:54:54[/TD]
[TD]Tue[/TD]
[TD="align: right"]4:56:35[/TD]
[/TR]
[TR]
[TD="align: right"]09/01/2018 09:17[/TD]
[TD="align: right"]09/01/2018 09:29[/TD]
[TD="align: right"]0:12:21[/TD]
[TD]Thu[/TD]
[TD="align: right"]0:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]18/01/2018 03:18[/TD]
[TD="align: right"]18/01/2018 03:47[/TD]
[TD="align: right"]0:29:02[/TD]
[TD]Wed[/TD]
[TD="align: right"]3:41:37[/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2018 10:23[/TD]
[TD="align: right"]31/01/2018 10:23[/TD]
[TD="align: right"]0:00:32[/TD]
[TD]Wed[/TD]
[TD="align: right"]0:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2018 10:25[/TD]
[TD="align: right"]31/01/2018 14:35[/TD]
[TD="align: right"]4:10:20[/TD]
[TD]Mon[/TD]
[TD="align: right"]0:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]26/02/2018 11:55[/TD]
[TD="align: right"]26/02/2018 15:45[/TD]
[TD="align: right"]3:50:20[/TD]
[TD]Mon[/TD]
[TD="align: right"]0:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]26/02/2018 15:48[/TD]
[TD="align: right"]26/02/2018 15:57[/TD]
[TD="align: right"]0:08:58[/TD]
[TD]Fri[/TD]
[TD="align: right"]0:00:00[/TD]
[/TR]
[TR]
[TD="align: right"]02/03/2018 05:09[/TD]
[TD="align: right"]02/03/2018 11:44[/TD]
[TD="align: right"]6:34:47[/TD]
[TD]Mon[/TD]
[TD="align: right"]1:50:25[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 717"]
<tbody>[TR]
[TD="class: xl65, width: 108, align: right"]02/03/2018 01:28[/TD]
[TD="class: xl65, width: 111, align: right"]04/03/2018 13:49[/TD]
[TD="class: xl66, width: 78, align: right"]60:20:38[/TD]
[TD="width: 64"]Mon[/TD]
[TD="class: xl66, width: 78, align: right"]34:31:12[/TD]
[TD="class: xl66, width: 214, align: right"][/TD]
[TD="class: xl66, width: 64, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 439"]
<tbody>[TR]
[TD="align: right"]18/12/2017 23:16[/TD]
[TD="align: right"]18/12/2017 23:21[/TD]
[TD="align: right"]0:05:01[/TD]
[TD]Mon[/TD]
[TD="align: right"]2:21:06[/TD]
[/TR]
[TR]
[TD="align: right"]20/12/2017 01:01[/TD]
[TD="align: right"]20/12/2017 01:06[/TD]
[TD="align: right"]0:05:00[/TD]
[TD]Wed[/TD]
[TD="align: right"]5:58:21[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 439"]
<tbody>[TR]
[TD="class: xl65, width: 108, align: right"]18/03/2018 21:03[/TD]
[TD="class: xl65, width: 111, align: right"]18/03/2018 21:29[/TD]
[TD="class: xl66, width: 78, align: right"]0:25:14[/TD]
[TD="width: 64"]Sun[/TD]
[TD="class: xl66, width: 78, align: right"]5:29:12[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Error in my thinking. This is better,
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
T
  • Question Question
VBA help to count Daytime,Nighttime and Summary
2 3
Replies
20
Views
890
Alex Blakenburg
A
O
  • Solved
Travel Duration allowing for more than one day and different time zones
Replies
7
Views
151
myall_blues
myall_blues
AOB
  • Question Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
718
AOB
AOB
F
  • Question Question
Excel Formula for Dynamic Timetable with Variable Time Increments based on certain hours of the day
Replies
1
Views
664
Muhammad_Usman
Muhammad_Usman

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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