Date & Time
Get the Nth weekday of a month (e.g., 4th Thursday of November)
Calculate floating holidays and recurring monthly board dates from Year, Month, Nth occurrence, and ISO weekday—with a 5th-week guard.
Find the 1st occurrence on or after day 1, then add (N - 1) * 7
Starting at firstDay = DATE(Year, Month, 1), the first occurrence of IsoDow (1 = Mon .. 7 = Sun) falls at firstDay + MOD(IsoDow - WEEKDAY(firstDay, 2) + 7, 7).
Adding (Nth - 1) * 7 jumps to the 2nd, 3rd, or 4th occurrence. Because not every month has 5 Thursdays, checking MONTH(result) <> Month catches overflow into the next month.
Use a valid four-digit year, integer month 1–12, occurrence 1–5 and weekday 1–7. The month check detects a missing fifth occurrence, not every invalid input. TEXT returns a label rather than a numeric date.
| Rule | Yr / Mo / Nth / IsoDow | 1st Match | Nth Match Date |
|---|---|---|---|
| US Thanksgiving 2026 | 2026 / 11 / 4 / 4 (Thu) | 2026-11-05 | 2026-11-26 |
| MLK Day 2026 | 2026 / 1 / 3 / 1 (Mon) | 2026-01-05 | 2026-01-19 |
| 5th Thu Nov 2026 | 2026 / 11 / 5 / 4 (Thu) | 2026-11-05 | No 5th weekday |
Month-guarded Nth weekday formula
November 1, 2026 is Sunday (ISO 7), so the 1st Thursday is November 5 and the 4th Thursday is November 5 + 21 = 2026-11-26.
=LET(firstDay, DATE(A2, B2, 1), res, firstDay + MOD(D2 - WEEKDAY(firstDay, 2) + 7, 7) + (C2 - 1) * 7, IF(MONTH(res)<>B2, "No Nth weekday", TEXT(res, "yyyy-mm-dd")))3rd Monday of January check
Row 3 computes the 3rd Monday of January 2026 (2026-01-19).
=LET(firstDay, DATE(A3, B3, 1), res, firstDay + MOD(D3 - WEEKDAY(firstDay, 2) + 7, 7) + (C3 - 1) * 7, IF(MONTH(res)<>B3, "No Nth weekday", TEXT(res, "yyyy-mm-dd")))Try the example
Use the original values or change them to test your understanding.