CODEWITHSUNDEEP
excelexcel-formulaformuladowntime
steviemac2000
steviemac2000

Reputation: 55

What is a formula for calculating overlapping downtime periods in Excel?

I have periods of downtime per calendar month and I wish to calculate the total:

Outage Start Outage End Outage (mins)
05/10/2021 15:00 05/10/2021 18:00 180
06/10/2021 16:00 06/10/2021 18:00 120
06/10/2021 17:00 06/10/2021 19:00 120
07/10/2021 16:00 07/10/2021 18:00 120
25/10/2021 08:00 25/10/2021 09:32 92

Summing the last column gives 632, but this is inaccurate as the period of downtime specified by row 3 overlaps that specified by row 2. I need a formula which takes this into account and gives the correct answer of 572.

Upvotes: 0

Views: 385

Answers (2)

basic
basic

Reputation: 11968

If you need a copyable formula, then use

=(MIN(B2,A3)-A2)*24*60

if you need a total amount in one number, use this array formula:

=SUM(((IF((B2:B6<A3:A7),B2:B6,IF(A3:A7<>"",A3:A7,B2:B6)))-A2:A6))*24*60

enter image description here

Upvotes: 0

Gangula
Gangula

Reputation: 7332

Assuming that

  • your rows are in ascending order
  • and the Start and End columns are of Date/time data type

You can check if the current start time is in between previous start & end and then calculate the remaining time.

=IF(AND(A2>A1,A2<B1),B2-B1,B2-A2)*1440

Outage difference working formula screenshot

Upvotes: 0

Related Questions