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:
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:
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)))
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...