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;
<tbody>
</tbody>
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;
Event | Time Stamp Start | Time Stamp End | Duration | Start Day | Hours outside Normal Operation (Off-peak) | |||||||
1 |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | Fri | ||||||||
2 |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | ||||||||
3 |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | Thu | ||||||||
4 |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | Fri | ||||||||
5 |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | ||||||||
6 |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody>
</tbody>
Thanks in advance for any help with this.
Chev