0

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.

steviemac2000
  • 35
  • 5

2 Answers2

0

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

Gangula
  • 5,193
  • 4
  • 30
  • 59
0

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

basic
  • 11,673
  • 2
  • 9
  • 26