Dates & Schedules
Test Whether a Schedule Range Contains a Specific Date (Date & Timestamp Safe)
Check whether a target calendar date appears inside a holiday or blackout range using COUNTIFS whole-day bounds so fractional timestamps never cause missed matches.
Guard against fractional timestamps when testing date membership
When every cell in A2:A6 is a pure midnight date serial (such as DATE(2026, 7, 3)), COUNTIF(A2:A6, B8) > 0 works. However, if an imported log stores DATE(2026, 7, 3) + TIME(14, 30, 0) (serial 46206.60417), exact COUNTIF against midnight 46206 returns 0!
Testing a half-open 1-day interval COUNTIFS(A2:A6, ">=" & INT(B8), A2:A6, "<" & (INT(B8) + 1)) > 0 matches both pure midnight dates AND any timestamp occurring on that calendar day without touching adjacent days.
| Blackout Entry | Stored Value | Exact COUNTIF(DATE(2026,7,3)) | Day-Window COUNTIFS Match? |
|---|---|---|---|
| New Year Observe | 2026-01-01 00:00 | 0 | No |
| Spring Maintenance | 2026-04-19 00:00 | 0 | No |
| Midyear Freeze (2:30 PM) | 2026-07-03 14:30 | 0 (Missed!) | Yes (Matched!) |
| Autumn Cutover | 2026-10-12 00:00 | 0 | No |
| Year-End Freeze | 2026-12-24 00:00 | 0 | No |
Timestamp-Safe Date Membership Flag
Returns "Blackout Date" when any date or datetime in B2:B6 falls on calendar day B8, or "Open Date" otherwise.
=IF(NOT(ISNUMBER(B8)), "Valid Date Required", IF(COUNTIFS(B2:B6, ">=" & INT(B8), B2:B6, "<" & (INT(B8) + 1)) > 0, "Blackout Date", "Open Date"))Count of Events Falling on Target Calendar Day
Counts how many entries in B2:B6 occur between 00:00:00 and 23:59:59.999 on date B8.
=COUNTIFS(B2:B6, ">=" & INT(B8), B2:B6, "<" & (INT(B8) + 1))Try the example
Use the original values or change them to test your understanding.