This formula allows me to calculate the duration between 4 different times, it is useful for instance if I want to know how long an outage has impacted a shop.

 

When I use the formula ?

To calculate the impact duration of an issue.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How arethe formula ?

=IF()

=MEDIAN()

This is the formula:

=IF(C2=MEDIAN(A2,B2,C2),IF(D2=MEDIAN(A2,B2,D2),D2-C2,B2-C2),IF(D2=MEDIAN(A2,B2,D2),D2-A2,IF(A2=MEDIAN(C2,D2,A2),B2-A2,0)))

Before to apply it, I need to add more data in my sheet because if I apply the formula without doing extra work, I will get this:

As we can see, the line 3 and 4 don’t show correctly, this is because excel considers that all times are in the same date and for me, it should not because the time 01:00:00 should be next day.

To check, I can do this simple thing, change the format from “time” to “short date” or if I want both, customize my format like that “dd/mm/yyyy hh:mm:ss”.

As you may guess, first, I will add a column with the date then I will delete all data in the columns D and E and put this formula in:

• The cell D2: =A2+15/24 -> 15 is the open time
• The cell E2: =D2+10/24 -> 10 is how many hours the shop is open

Copy them to the below cells because the shop has the same time schedule. As we can see, the date beside 01:00:00 shows the next day. Now, I will do the same thing for the column B and C:

• The cell B2: =A2+4/24 -> 4 is when the outage starts
• The cell C2: =B2+2/24 -> 2 is how many hours the outage lasts

For those columns, I will not copy the formula to the below lines because the outage is different, I just have to change 4 and 2 with the correct time. Now, the formula shows the correct duration. The fact to add the date column, the formula has changed automatically and normally, it should be:

=IF(D2=MEDIAN(B2,C2,D2),IF(E2=MEDIAN(B2,C2,E2),E2-D2,C2-D2),IF(E2=MEDIAN(B2,C2,E2),E2-B2,IF(B2=MEDIAN(D2,E2,B2),C2-B2,0)))