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