Calculating Downtime During Hours of Operation

Hi All,

I’m looking for assistance to help calculate incident down time relative to the hours of business operation. Incident downtime is only calculated during the business hours with the incident suspending outside of the contracted business hours. Using the forum threads I was able to identify a formula to calculate downtime during a standard business day Monday to Friday (8.5hrs x 5 days) with the result populated in cell H2 of the attached spreadsheet (let me know if I have missed the mark ).

The complexity comes with non-standard business hours, such as a shopping mall which is open 7 days a week with different operating hours per day. Using row 3 as the example, manually calculating the downtime gives 1890 minutes or 31.5hrs of downtime. Is there a formula to support this calculation within cell H3?

Some of the post I have read use a reference table broken in to the seven days of the week with corresponding hours of operation, a formula then calculates the downtime relative to the each day of the week, the daily components of downtime are then summed to give total downtime in the week.

Unfortunately I’m looking at 1000’s of lines of incidents, grouped by hours of operation, and hope there is a formula I can paste into H3 and then drag down etc.

Thanks in advance.

Fantastic! Thank you so much, a great help

hello, im new and trying to understand this part of your formula.
what does this do?

CHOOSE(WEEKDAY(E3,2),9,9,9,9,9,9,10)

im assuming its hours in a given work week but in your given formula, is it supposed to be the hours not worked?