Hello all! I'm trying to calculate the outage duration for when a website is down. We need to know, of the total outage duration, which hours fall into "PEAK hours" and which hours fall within "NON-PEAK Hours" criteria range.
Spreadsheet attached. See the sheet "Testing" in this file.
In the example, sheet "Testing" row 2
The outcome should be
Total duration hours for peak and non-peak time frames
PEAK (duration) 2:00
NON-PEAK (duration) 13:00
15:00 <--Grand total of entire duration
The example uses the following start and stop times:
Start of outage | End of outage
9/27/16 16:00 | 9/28/16 7:00
Cell B9 is 2 hours, because within the total outage duration, 1 hour falls within the "PEAK" range criteria (9/27 4pm - 5pm), and on the 2nd day 9/28, another hour also falls within the PEAK criteria, (6am - 7am) for a total of a 2 hour duration for the PEAK category.
Cell B10 is 13 hours because the remainder of the outage hours fall within the NON-PEAK criteria
However, if 9/28/16 was a holiday, then that 2nd hour from 6am-7am would have to be counted in NON-PEAK hours instead of PEAK hours (so, the new totals would be 1 hour peak, 14 hours non-peak).
Category criteria
PEAK HOURS = M-F 6am - 5pm
NON-PEAK HOURS = all other hours/days (5pm and on for M-F) and weekends (sat & sun) and any holidays (which I will list in a column)
Any help MUCH appreciated, I've been quite stuck for a while and keep trying various options and researching....I've looked into several functions (e.g., TIME, TEXT, WORKDAY, etc)
If the outage range did not span days, and were only within 1 day (start date and stop date were the same), then this would work: =MIN(D6,TIME(17,0,0))-MAX(C6,TIME(6,0,0)) to calculate PEAK category, but since outages can span days, this doesn't work.
I think I need a conditional formula that says to give the duration based on the PEAK and NONPEAK criteria.
File attached here.
Bookmarks