Date & Time
Get the last working day of the month (skipping weekends and holidays)
Step backward 1 business day from the 1st of next month with WORKDAY(EOMONTH(date, 0) + 1, -1, holidays) for payroll and close dates.
Why WORKDAY(EOMONTH(date, 0), 0) does not work—and +1, -1 does
Passing 0 days to WORKDAY leaves a Sunday date untouched instead of rolling backward to Friday! By stepping to EOMONTH(A2, 0) + 1 (the 1st calendar day of NEXT month) and asking WORKDAY(..., -1, Holidays) for 1 business day earlier, the engine backs over Sunday, Saturday, and any bank holidays automatically.
In May 2026, May 31 falls on Sunday: WORKDAY(2026-06-01, -1) lands on Friday 2026-05-29—and if May 29 is a company holiday, it steps back to Thursday 2026-05-28.
If the whole month is excluded, the result can fall in the preceding month; check that boundary when at least one working day in the target month is required. TEXT produces a display label.
| RefDate (A) | Month End | No Holiday | With May 29 Holiday (B2) |
|---|---|---|---|
| 2026-05-10 | 2026-05-31 (Sun) | 2026-05-29 (Fri) | 2026-05-28 (Thu) |
| 2026-04-10 | 2026-04-30 (Thu) | 2026-04-30 (Thu) | 2026-04-30 (Thu) |
Last business day of month formula
Supply the applicable holiday dates explicitly as WORKDAY’s third argument. The function assumes Saturday/Sunday weekends and does not fetch bank or company calendars.
=TEXT(WORKDAY(EOMONTH(A2, 0) + 1, -1), "yyyy-mm-dd")Holiday-aware variant
Adding Holiday B2 (2026-05-29) shifts May's final working day to 2026-05-28.
=TEXT(WORKDAY(EOMONTH(A2, 0) + 1, -1, B2:B2), "yyyy-mm-dd")Try the example
Use the original values or change them to test your understanding.