This formula allows me to calculate the duration between 2 time schedules and 2 time frames based in different countries by taking in account the time zone, 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 per country.

 

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 are the formula ?

=IF()

=MEDIAN()

Before to reach the result shows in the picture above, normally, I start with few data, something like that:

Since I am located in Spain, the outage time is in CET time zone. The first things I will do, it is to fill up the cells:

• Column D: I use a tool found in internet to check the difference between my country and the rest
• In cell A3: =$A$2+D3/24 then copy to below cells
• In cell B3: =$B$2+D3/24 then copy to below cells
• Column E and F: no matter which country, the open/close times should be in the local time, to make easier, I put that all shops have the same time schedule. This is because the outage time will be converted to the local time with the formula A3 and B3

As we can see, B5 doesn’t show the time correctly, this is because with the data I have, excel considers that all time (except 1) have the same date so it doesn’t take in account the after day of the outage.

First, I will add a date column showing the date of the outage then:

• In cell A3: =INT(B3) then copy to below cells -> format the cell in "short date"
• In cell B3: =$A$2+($B$2+E3/24) then copy to below cells
• In cell C3: =$A$2+($C$2+E3/24) then copy to below cells
• In cell F3: =A3+15/24 -> 15 is the open time
• In cell G3: =F3+10/24 -> 10 is how many hours the shop is open

It is better but still values in column C are not correct. This is because I simply convert the time without taking in account the duration of the outage. This is the formula for the outage duration:

=($C$2-$B$2+($C$2<$B$2))*24

If I put this formula in any cell, for instance in cell J1, the result will be 6 so let´s incorporate it in the formula of my cell C3, this is the new formula:

=B3+(($C$2-$B$2+($C$2<$B$2))*24)/24

With this last modification, the results are correct and here the formula:

=IF(B3=MEDIAN(F3,G3,B3),IF(C3=MEDIAN(F3,G3,C3),C3-B3,IF(G3-B3<0,0,G3-B3)),IF(C3=MEDIAN(F3,G3,C3),C3-F3,IF(F3=MEDIAN(B3,C3,F3),G3-F3,0)))