# Time Formula

• Hi,

I have the following sheet that calculates the downtime in branches based on the specific operating hours everyday.

The downtime ahould only take into account during the operating hours and should exclude hours of downtime during non operating hours.

In the example attached, the actual downtime during operating hours is 14 hours and 32 minutes. Although the downtime lasted from Feb 14 to Feb 16, the downtime hours that affected operating hours was 14 hours 32 minutes and not 36 hours and 8 minutes.

Is there anyway we could build a formula for this ?

Appreciate all the help.

I have a cross pots here:

http://www.mrexcel.com/forum/s…php?p=2627565#post2627565

## Files

• Re: Time Formula

Hi,

I hope I am getting this right.

The downtime occurred on Feb 14 on 8.58 pm. The operating hours for this branch on Monday is between 9.30 am to 10.30 pm. For Feb 14, based on the operating hours on Monday, the downtime only affected the outlet from 8.58 pm to 10.30 pm which is 1 hour and 32 minutes.
On Feb 15, the operating hours for the branch is still from 9.30 am to 10.30 pm. Therefore it is 13 hours.
The downtime finally stopped on Feb 16 at 9.06 am. However, this did not affect the branch since the branch only opens at 9.30 am on Feb 16 which is a Wednesday.

Therefore, in total, the downtime hours that affected the operating hours of the branch is 14 hours and 32 minutes.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!