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:
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:
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)))
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...