Date & Time
Next & Previous Working Day: Strict Step vs. Same-or-Next Business Day Roll
Calculate strictly next/previous working days and roll-forward/roll-backward settlement dates that preserve valid business days while skipping weekends and holidays via WORKDAY.
Strict +1/-1 business-day step vs banking Following / Preceding conventions
WORKDAY(start_date, 1, holidays) always advances at least one full business day—even when start_date is already a Tuesday. That behavior is right for T+1 settlement, but wrong for contractual due dates that should stay on start_date when it is already a working day and only roll when it hits a Saturday, Sunday, or holiday.
Subtracting 1 calendar day before stepping +1 business day—WORKDAY(date - 1, 1, holidays)—implements the "Following Business Day" convention: weekdays that are not holidays remain unchanged, while weekends and holidays roll forward to the next open business day. Symmetrically, WORKDAY(date + 1, -1, holidays) implements the "Preceding Business Day" roll-backward convention.
| Rule | Formula Pattern | Saturday 2026-07-04 (Fri Jul 3 Holiday) | Tuesday 2026-07-07 |
|---|---|---|---|
| Strictly Next Working Day | WORKDAY(A2, 1, $F$2:$F$3) | 2026-07-06 (Mon) | 2026-07-08 (Wed) |
| Strictly Previous Working Day | WORKDAY(A2, -1, $F$2:$F$3) | 2026-07-02 (Thu) | 2026-07-06 (Mon) |
| Roll-Forward (Same or Next) | WORKDAY(A2 - 1, 1, $F$2:$F$3) | 2026-07-06 (Mon) | 2026-07-07 (Tue) |
| Roll-Backward (Same or Prev) | WORKDAY(A2 + 1, -1, $F$2:$F$3) | 2026-07-02 (Thu) | 2026-07-07 (Tue) |
Same-or-Next Business Day Roll-Forward Formula
Shift the candidate date back by 1 day and ask WORKDAY for +1 working day against your holiday table so valid business days map to themselves and non-working dates advance cleanly.
=TEXT(WORKDAY(A2 - 1, 1, $F$2:$F$3), "yyyy-mm-dd")Same-or-Previous Business Day Roll-Backward Formula
For payroll funding cutoffs that must clear on or before a target calendar date, shift forward by 1 day and step -1 working day.
=TEXT(WORKDAY(A2 + 1, -1, $F$2:$F$3), "yyyy-mm-dd")Try the example
Use the original values or change them to test your understanding.