Dates & Time
Count Calls or Log Events Inside a Specific Time-of-Day Window (COUNTIFS)
Count support calls, ER arrivals, or API requests occurring inside an intraday shift bucket (such as >= 09:00 and < 12:00) using half-open [Start, End) COUNTIFS intervals.
Use half-open [Start, End) time intervals so boundary events never double-count!
Staffing call centers, retail checkout lanes, and hospital triage desks requires bucketing arrival times into intraday blocks—such as 09:00–12:00, 12:00–15:00, and 15:00–18:00! A classic spreadsheet bug is pairing ">=" & Start with "<=" & End: any call arriving at exactly 12:00:00 gets counted TWICE (in both the 09:00–12:00 bucket AND the 12:00–15:00 bucket), inflating total arrivals!
Always pair inclusive lower bound ">=" & D2 with STRICT upper bound "<" & D3: COUNTIFS(B2:B7, ">=" & D2, B2:B7, "<" & D3)! A 09:00:00 call lands cleanly in [09:00, 12:00), while a 12:00:00 call rolls cleanly into [12:00, 15:00) with zero overlap and zero gap!
| Shift Window [Start, End) | COUNTIFS Criteria Pair | Matching Call Times in Log | Call Count |
|---|---|---|---|
| Morning [09:00, 12:00) | ">=" & 09:00, "<" & 12:00 | 09:00, 10:30, 11:55 (excludes 08:45 & 12:00!) | 3 calls |
| Midday [12:00, 15:00) | ">=" & 12:00, "<" & 15:00 | 12:00, 14:20 | 2 calls |
| Inverted Window (End <= Start) | D3 <= D2 Check | Guarded Input Error | "Invalid Time Window" |
Count Calls Within Half-Open Intraday Window [Start, End)
Verifies End > Start for same-day windows, then counts time serials in B2:B7 satisfying >= D2 and < D3.
=IF(D3 <= D2, "Invalid Time Window", COUNTIFS(B2:B7, ">=" & D2, B2:B7, "<" & D3))Share of Total Daily Calls Falling Inside Peak Window (%)
Divides the window call count by COUNTA(B2:B7) and expresses the share as a percentage.
=IF(OR(D3 <= D2, COUNTA(B2:B7) = 0), "Invalid Time Window", ROUND(100 * COUNTIFS(B2:B7, ">=" & D2, B2:B7, "<" & D3) / COUNTA(B2:B7), 1))Try the example
Use the original values or change them to test your understanding.