Statistical
Average Call Duration (AHT) Per Month with AVERAGEIFS & EOMONTH
Calculate monthly call-center Average Handle Time (AHT) between dynamic month start >= MonthStart and <= EOMONTH(MonthStart, 0) using AVERAGEIFS—with COUNTIFS zero-call protection.
Aggregate support-desk Average Handle Time (AHT) cleanly by calendar month!
Call-center Workforce Management (WFM) dashboards track Average Handle Time (AHT = Talk + Hold + After-Call Wrap-Up) month-over-month. Instead of hardcoding end-of-month dates like "<=2026-02-28" that break in leap years or 31-day months, store the 1st of each target month in E2 (e.g., "2026-03-01") and let EOMONTH(E2, 0) compute the exact last day of that month automatically!
Bracket your call-log date column A2:A7 inside AVERAGEIFS: ">= " & E2 and "<=" & EOMONTH(E2, 0)! Pairing AVERAGEIFS with a COUNTIFS(..., 0) pre-check avoids #DIV/0! on future unpopulated months!
| Target Month Start (E2) | Date Criteria Window | Matching Call Durations (min) | Monthly AHT |
|---|---|---|---|
| 2026-03-01 (March) | >= 2026-03-01 and <= 2026-03-31 | 6.4, 8.2, 5.8 min (3 calls) | 6.80 minutes |
| 2026-04-01 (April) | >= 2026-04-01 and <= 2026-04-30 | 7.5, 9.5 min (2 calls) | 8.50 minutes |
| 2026-05-01 (Future Month) | 0 matching calls | COUNTIFS = 0 guard | 0 (No #DIV/0!!) |
Monthly Average Handle Time (AHT) via AVERAGEIFS + EOMONTH
Averages call duration C2:C6 for all logs falling between month start E2 and EOMONTH(E2, 0), returning 0 when no calls exist.
=IF(COUNTIFS(A2:A6, ">=" & E2, A2:A6, "<=" & EOMONTH(E2, 0)) = 0, 0, ROUND(AVERAGEIFS(C2:C6, A2:A6, ">=" & E2, A2:A6, "<=" & EOMONTH(E2, 0)), 2))Queue-Specific Monthly AHT (Adding Queue Tier Criteria)
Filters simultaneously by calendar month [E2, EOMONTH(E2, 0)] and Queue Tier F2 ("Tier-1").
=ROUND(AVERAGEIFS(C2:C6, A2:A6, ">=" & E2, A2:A6, "<=" & EOMONTH(E2, 0), B2:B6, F2), 2)Try the example
Use the original values or change them to test your understanding.