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.
CodePudding user response：
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
CodePudding user response：
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
