Data Validation & Hygiene
Validate & Highlight Dates Strictly Within the Next 30 Days (Type-Safe Window)
Enforce or highlight scheduling and renewal dates falling inside a 30-day forward window [Anchor, Anchor + 30] while rejecting text strings, blanks, and past dates.
Why ISNUMBER(A2) is essential in custom date validation rules
In spreadsheet comparison order, every text string compares as "greater than" every number—so a naive A2 >= $D$2 check returns TRUE on accidental text like "TBD"! Worse, omitting the lower bound highlights past-due dates alongside upcoming dates.
Combining ISNUMBER(A2), A2 >= $D$2, and A2 <= $D$2 + $D$3 inside AND(...) guarantees that only genuine numeric date serials inside the inclusive [0, N] forward window return TRUE.
| Candidate Entry (Anchor = 2026-06-01) | Offset from Anchor | Validation / Highlight Result |
|---|---|---|
| 2026-06-01 (Same day) | 0 days | TRUE (Valid) |
| 2026-07-01 (30th day) | +30 days | TRUE (Valid upper boundary) |
| 2026-07-02 (31st day) | +31 days | FALSE (Too far ahead) |
| 2026-05-31 (Yesterday) | -1 day | FALSE (Past date) |
| "TBD" or Blank | Non-numeric | FALSE (Type-rejected) |
Inclusive Next-N-Days Validation & Highlight Rule
Accepts numeric date serials from Anchor Date ($D$2) through $D$2 + $D$3 inclusive.
=AND(ISNUMBER(A2), A2 >= $D$2, A2 <= $D$2 + $D$3)Strictly Future Tomorrow-Through-N-Days Variant
Change >= $D$2 to > $D$2 when same-day bookings are closed and the window starts tomorrow.
=AND(ISNUMBER(A2), A2 > $D$2, A2 <= $D$2 + $D$3)Try the example
Use the original values or change them to test your understanding.