Have start-stop times & duration, need sub-duration based on range criteria

[CathTyner]

Hello all! Stuck on a problem.
I have a spreadsheet which contains information about when a website outage occurred. Each row is an outage incident.
I can calculate the total duration of the outage.
I have a "start of outage" and a "end of outage" timestamp, format 3/10/16 16:30:00.
With the two fields ('start time' and 'end time') formatted as a date/time, I can just subtract the columns to get my duration of the outage.

E.g.,
A2 (Start time) 3/10/16 16:30:00

B2 (End time) 3/10/16 17:30:00

C2 (Duration) 1:00:00 (=B2-A2)

I need to further categorize these outages into 2 groups:

1. Peak Hours: Defined as an outage occurring M-F 6am - 5pm.
2. Non Peak Hours: All other days/hours; M-F 5:01pm onward, and weekends (Sat & Sun).

I need to know: Of the total outage duration, which hours/minutes fall into "peak hours" and which fall into non-peak hours?

NOTE: An outage may occur over several days. For example, an outage could occur Friday at 4pm and last until Monday at 7am, so there would be 2 hours within the "peak hours" category/cell/column (Friday 4pm-5pm & Monday 6am-7am) and the remaining duration would fall into non-peak hours.

For example, an outage like this:

Outage 1
Start: 3/10/16 16:30:00
End: 3/10/16 17:30:00
Duration: 1:00:00

Would be further categorized to describe peak hours:

"Outage Duration, Peak Hours": 30 minutes (from 4:30 - 5pm)

"Outage Duration, Non-Peak Hours: 30 minutes (from 5pm - 5:30pm)

The problem is that I have no idea how to create these 2 "peak hours/non-peak hours" columns. Any advice input would be VERY much appreciated!

 

[Gerald Higgins]

Hi, welcome to the board.

Question 1 - Just to be totally clear, when you say your dates are in the format 03/10/16, what exactly does that mean ?
DD/MM/YY ?
MM/DD/YY ?
Maybe even YY/MM/DD ?
Maybe something else ?

Question 2 - is it possible that an outage could last from, say, 5am Monday to 6pm the following Friday ?
If YES, do we need to count up ALL the peak hours in that period ?

 

[CathTyner]

Thank you Gerald!

1. Date format is DD/MM/YYY HH:MM:SS (however, seconds are never captured, they are always an input of "00".
2. See the "NOTE" in my original post - yes, this is possible, and yes, I need to account for all hours within an outage, with the final outcome being

1)total duration
2)of the "total duration" need time/duration within "peak hours" only.
3)of the "total duration" need time/duration within "non-peak" hours only.

NOTE: An outage may occur over several days. For example, an outage could occur Friday at 4pm and last until Monday at 7am, so there would be 2 hours within the "peak hours" category/cell/column (Friday 4pm-5pm & Monday 6am-7am) and the remaining duration would fall into non-peak hours.

 

[Gerald Higgins]

OK, I haven't used this function much myself, but take a look at the NETWORKDAYS() function.

I would look at your duration in three separate parts.

First, the start day - how many hours within that day are "peak" ?

Similarly, for the end day.

Then, for the days in between the start and end day (and specifically NOT including the start and end day), how many of those days are working days, which you can calculate using the above function.
You can then simply multiply that number, by the number of peak hours each standard working day.
For example, for an outage starting on Monday and ending on Friday, that HAS to involve three full working days (Tue/Wed/Thu) which means 3 full days' worth of peak hours, and 3 full days' worth of non-peak.