Date & Time
Sum durations above a minute threshold: full durations versus excess minutes
Compare the total duration of qualifying sessions with only their excess minutes, including thresholds longer than one day.
Two different duration measures
The full-duration measure includes every minute of sessions strictly longer than the threshold: 45 + 50 = 95 minutes. Excess duration includes only time beyond the allowance: (45 - 30) + (50 - 30) = 35 minutes. Neither measure defines a billing or SLA policy by itself.
Store durations as numeric fractions of a day, not text times or calendar timestamps. Divide a threshold in minutes by 1440; this also handles thresholds of 1440 minutes or longer without clock-time wrapping. This example requires all five durations and the threshold to be numeric and nonnegative. Results are rounded to two decimal places, not guaranteed exact arithmetic.
| Ticket (A) | Duration TIME (B) | Exceeds >30m? | Overage Past 30m (C) |
|---|---|---|---|
| TKT-101 | 00:18:00 (18 min) | No | 0 min |
| TKT-102 | 00:45:00 (45 min) | Yes (45 min) | 15 min |
| TKT-103 | 00:30:00 (30 min) | No (exact boundary!) | 0 min |
| TKT-104 | 00:50:00 (50 min) | Yes (50 min -> Sum = 95m) | 20 min (-> Sum = 35m) |
| TKT-105 | 00:12:00 (12 min) | No | 0 min |
Total minutes of all calls strictly exceeding E2 minutes
Use E2/1440 as the strict lower threshold in days. A duration exactly equal to the allowance is excluded.
=IF(OR(COUNT(B2:B6)<>5,COUNT(E2)<>1,MIN(B2:B6)<0,E2<0),"Invalid inputs",ROUND(SUMIFS(B2:B6, B2:B6, ">" & (E2 / 1440)) * 1440, 2))Marginal SLA overage minutes above E2 per call
Each helper rounds excess minutes for one session to two decimals before summing. Rounding the grand total instead can produce a different result; choose a documented policy for your application.
=IF(OR(COUNT(B2:B6)<>5,COUNT(E2)<>1,MIN(B2:B6)<0,E2<0),"Invalid inputs",SUM(C2:C6))Try the example
Use the original values or change them to test your understanding.