Date & Time
Calculate the Nth or last weekday of a month
Build a recurring monthly schedule and detect when a requested fifth weekday does not exist.
Monthly weekday scheduling with a missing-occurrence check
Start at the first day of the month, advance zero to six days to the requested weekday, then add seven days for each further occurrence. A fifth occurrence may fall in the next month: the formula returns No occurrence instead of silently changing months.
This is calendar arithmetic, not an official holiday, exchange or release calendar. Observed holidays and business-day adjustments are separate rules. The example uses whole-number years 1901–9998, months 1–12, occurrences 1–5 and weekdays 1=Monday through 7=Sunday.
| 2026 Floating Event | Inputs (Year, Month, N, ISODay) | Computed 2026 Date |
|---|---|---|
| US Thanksgiving (4th Thu of Nov) | 2026, 11, N=4, Thu=4 | 2026-11-26 |
| MLK Jr. Day (3rd Mon of Jan) | 2026, 1, N=3, Mon=1 | 2026-01-19 |
| Third Friday of June (unadjusted calendar date) | 2026, 6, N=3, Fri=5 | 2026-06-19 |
| Memorial Day (Last Mon of May) | EOMONTH(2026-05-01, 0) back to Mon | 2026-05-25 |
Exact Nth weekday of any Year (A2) and Month (B2)
C2 is occurrence N (1..5) and D2 is ISO weekday (1=Monday .. 4=Thursday .. 7=Sunday).
=IF(OR(OR(NOT(ISNUMBER(A2)),A2<>INT(A2),A2<1901,A2>9998,NOT(ISNUMBER(B2)),B2<>INT(B2),B2<1,B2>12,NOT(ISNUMBER(D2)),D2<>INT(D2),D2<1,D2>7),NOT(ISNUMBER(C2)),C2<>INT(C2),C2<1,C2>5),"Invalid inputs",LET(monthStart,DATE(A2,B2,1),targetDate,monthStart+MOD(D2-WEEKDAY(monthStart,2),7)+(C2-1)*7,IF(MONTH(targetDate)<>B2,"No occurrence",TEXT(targetDate,"yyyy-mm-dd"))))Last occurrence of weekday D2 in the month (e.g., Memorial Day = Last Monday of May)
Steps backward 0–6 days from EOMONTH(DATE(A2, B2, 1), 0) to hit the final occurrence whether the month has 4 or 5 Mondays.
=IF(OR(NOT(ISNUMBER(A2)),A2<>INT(A2),A2<1901,A2>9998,NOT(ISNUMBER(B2)),B2<>INT(B2),B2<1,B2>12,NOT(ISNUMBER(D2)),D2<>INT(D2),D2<1,D2>7),"Invalid inputs",LET(monthEnd,EOMONTH(DATE(A2,B2,1),0),TEXT(monthEnd-MOD(WEEKDAY(monthEnd,2)-D2,7),"yyyy-mm-dd")))Try the example
Use the original values or change them to test your understanding.