How to move a date that falls on a weekend to the next business day in Excel
Formula (Excel and Google Sheets)
=WORKDAY(B2-1,1,$E$2:$E$3)Step back one day and forward one working day: =WORKDAY(B2-1,1,holidays). Weekdays stay put; weekends and holidays move forward.
- Result on the example
- one result per row in
C2, filled down toC7 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C7. Change any input and the result updates, or edit the formula to experiment.
How it works
Going back one day and then forward one working day lands on the date itself when it is already a business day, and on the next business day when it isn't. The loan payment on Saturday, September 5 moves past Labor Day to Tuesday, September 8.
=WORKDAY(B2-1,1,$E$2:$E$3)Result8/3/2026 (46237)
B28/1/2026The value in B2.
B2-146234The - operator subtracts.
$E$2:$E$32 rows × 1 col: 9/7/2026, 10/12/2026A range: 2 cells in one column.
WORKDAY(B2-1,1,$E$2:$E$3)8/3/2026 (46237)WORKDAY: Date a number of workdays away.- start_date
B2-146234 - days
1 - holidays
$E$2:$E$32 rows × 1 col: 9/7/2026, 10/12/2026
- start_date
In Google Sheets
The same formula works in Google Sheets: =WORKDAY(B2-1,1,$E$2:$E$3). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(WEEKDAY(B2,2)>5,B2+8-WEEKDAY(B2,2),B2) | 8/3/2026, 8/14/2026, 9/7/2026…Different result | Handles weekends only, in any version: Saturday and Sunday move to Monday. Ignores holidays, so September 5 moves to Monday, September 7. |
Common mistakes
=WORKDAY(B2,1)always moves the date, even when it's already a weekday. TheB2-1is what keeps weekday dates where they are.- Banks observe weekend holidays on a nearby weekday. List the observed date, not the calendar date, in the holiday range.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Move a date that falls on a weekend to the next business day
C2, return the scheduled date if it is a business day, or else the next business day, skipping the holidays in E2:E3. Fill down to C7.Questions
How do I move it to the previous business day instead?
Mirror it: =WORKDAY(B2+1,-1,$E$2:$E$3) moves a Saturday back to Friday.
How do I just check whether a date is on a weekend?
=WEEKDAY(B2,2)>5 is TRUE for Saturday and Sunday.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.