Date & Time Formulas
Validate Weekday-Only Dates Within a Next-30-Days Booking Window
Enforce multi-rule scheduling validation—requiring a real numeric date inside [Anchor_Date, Anchor_Date + Window_Days] that also lands Monday–Friday via WEEKDAY(Date, 2) <= 5.
Combine rolling booking-horizon bounds and Monday–Friday business-day checks in one rule!
Appointment schedulers, freight dock calendars, and SLA portals typically enforce three simultaneous rules on a requested date (cell B2): (1) the entry must be a genuine numeric serial date ISNUMBER(B2), (2) it cannot fall in the past (< Anchor_Date B3) or beyond the allowed booking horizon (> B3 + Window_Days B4, such as next 30 days), and (3) it must land on a Monday–Friday weekday!
Passing return_type = 2 into WEEKDAY(B2, 2) numbers Monday = 1 through Friday = 5 and Saturday = 6, Sunday = 7—making WEEKDAY(B2, 2) <= 5 the cleanest international weekday test! Look at Anchor Date 2026-05-01 with a 30-day window (through 2026-05-31): Wednesday 2026-05-13 passes all three checks, Saturday 2026-05-16 fails the weekday gate, and Monday 2026-06-08 fails the 30-day horizon!
| Requested Date B2 (Anchor 2026-05-01, Window 30d) | In [May 1, May 31]? | WEEKDAY(B2, 2) | Validation Outcome |
|---|---|---|---|
| 2026-05-13 (Wednesday) | Yes (+12 days) | 3 (<= 5 Weekday) | "Valid Business Booking Date" |
| 2026-05-16 (Saturday) | Yes (+15 days) | 6 (> 5 Weekend) | "Reject: Weekend Date" |
| 2026-06-08 (Monday) | No (+38 days > 30) | 1 (Weekday) | "Reject: Outside Booking Window" |
Boolean Data-Validation Rule Formula (TRUE = Allowed, FALSE = Blocked)
Paste directly into Custom Data Validation to require a numeric Monday–Friday date within [B3, B3 + B4].
=AND(ISNUMBER(B2), ISNUMBER(B3), B4 >= 0, B2 >= B3, B2 <= B3 + B4, WEEKDAY(B2, 2) <= 5)Diagnostic Booking Rejection Message Formula
Pinpoints the exact reason a submitted date was rejected (non-date, out-of-window, or weekend).
=IF(OR(NOT(ISNUMBER(B2)), NOT(ISNUMBER(B3)), B4 < 0), "Invalid Date Input", IF(OR(B2 < B3, B2 > B3 + B4), "Reject: Outside Booking Window", IF(WEEKDAY(B2, 2) > 5, "Reject: Weekend Date", "Valid Business Booking Date")))Try the example
Use the original values or change them to test your understanding.