Calculate the duration between 2 time schedules and 2 time frames with a formula in an excel report

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.

formula excel


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 ?



This is the formula:


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:

formula excel

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
formula excel

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
formula excel

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:


formula excel

Interesting Management