Date & Time
Count Times Within a Specific Time Window (Daytime & Overnight COUNTIFS)
Count arrival, call, or transaction times between Start_Time and End_Time—handling both standard daytime windows (09:00–17:00) and cross-midnight graveyard shifts (22:00–06:00).
Why naive COUNTIFS returns ZERO on overnight 22:00–06:00 shifts!
Spreadsheet times are fractions of a 24-hour day (06:00 = 0.25, 09:00 = 0.375, 17:00 = 0.708333, 22:00 = 0.916667). For a daytime shift where Start <= End (09:00 to 17:00), COUNTIFS(B2:B8, ">=" & E2, B2:B8, "<=" & F2) works directly. But for a cross-midnight night shift (22:00 to 06:00, where 0.9167 > 0.25), NO time of day is simultaneously >= 22:00 AND <= 06:00—so naive COUNTIFS always returns 0!
Branching on IF(E2 <= F2, COUNTIFS(...), COUNTIF(B2:B8, ">=" & E2) + COUNTIF(B2:B8, "<=" & F2)) handles both daytime AND cross-midnight windows seamlessly with a single formula!
| Shift Window [Start .. End] | Decimal Time Comparison | Active Branch | Matching Ticket Count |
|---|---|---|---|
| Day Window 09:00 to 17:00 | 0.375 <= t <= 0.7083 | COUNTIFS(>= Start, <= End) | 4 daytime tickets |
| Night Window 22:00 to 06:00 | t >= 0.9167 OR t <= 0.2500 | COUNTIF(>= Start) + COUNTIF(<= End) | 2 overnight tickets |
| Exact Point Window 09:00 to 09:00 | 0.375 <= t <= 0.375 | COUNTIFS exact timestamp match | 1 ticket at 09:00 |
Daytime & Cross-Midnight Safe Time-Window Counter
Automatically switches from AND-bounded COUNTIFS (when Start_Time E2 <= End_Time F2) to OR-summed COUNTIF + COUNTIF when the window wraps across midnight.
=IF(E2 <= F2, COUNTIFS(B2:B8, ">=" & E2, B2:B8, "<=" & F2), COUNTIF(B2:B8, ">=" & E2) + COUNTIF(B2:B8, "<=" & F2))Share of Events Falling Inside Window (%)
Divides the window count by COUNT(B2:B8) to report staffing load share as a rounded percentage.
=ROUND(100 * IF(E2 <= F2, COUNTIFS(B2:B8, ">=" & E2, B2:B8, "<=" & F2), COUNTIF(B2:B8, ">=" & E2) + COUNTIF(B2:B8, "<=" & F2)) / COUNT(B2:B8), 1)Try the example
Use the original values or change them to test your understanding.