Formula Errors

Excel Formula to Move Weekend Due Dates Back to Friday

Ayan Masood Sep 25, 2026 869 views

Question details

The user needs to modify a due-date calculation in Excel so that any resulting date falling on a weekend (Saturday or Sunday) is automatically adjusted to the preceding Friday.

Product: Excel
Device & OS: not provided
Scenario: Calculating accurate project deadlines or payment due dates where weekends are considered non-working days and deadlines must be met on the last valid weekday.
Observed behavior: Standard date addition or interval formulas result in dates that land on weekends, requiring a dynamic formula to adjust them backward to a working day.

Before you start

Ensure that your source dates are formatted as actual Date values in Excel (not text strings) and note the cell references containing the dates you wish to evaluate.

Solution 1Recommended

Adjust Existing Dates Using CHOOSE and WEEKDAY Functions

This method evaluates a specific date cell and subtracts the exact number of days needed to revert a Saturday or Sunday to a Friday.

The WEEKDAY function evaluates your date and assigns it a number. By using '2' as the return type, Monday is 1 and Sunday is 7. The CHOOSE function then looks at that number and dictates how many days to subtract: 1 day for Saturday, 2 days for Sunday, and 0 for weekdays.

Select the target cell

Click on the cell where you want the adjusted due date to appear.

Input the formula

Assuming your original calculated date is in cell D6, type the following formula: =D6-CHOOSE(WEEKDAY(D6,2),0,0,0,0,0,1,2)

Apply and format

Press Enter to calculate the result. If the output appears as a serial number (e.g., 44500), right-click the cell, select 'Format Cells', and apply a 'Date' format.

Copy down the column

Drag the fill handle at the bottom right of the cell to apply this adjustment formula to other dates in your list.

Adjust Existing Dates Using CHOOSE and WEEKDAY Functions

Alternative Approach: You can also achieve this with a simpler IF statement: =IF(WEEKDAY(D6,2)=6, D6-1, IF(WEEKDAY(D6,2)=7, D6-2, D6))

Solution 2

Calculate Interval-Based Due Dates Using WORKDAY and EDATE

Best for generating new due dates based on monthly intervals from a starting date, while automatically bypassing weekends and holidays.

Manage Dates Effortlessly with WPS Spreadsheet

Calculate Complex Due Dates Easily in WPS Office

WPS Spreadsheet fully supports advanced Excel date functions, including WEEKDAY, CHOOSE, WORKDAY, and XLOOKUP. You can easily manage project timelines, skip weekends, and account for holidays without worrying about compatibility issues.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the project start dates.
2. Access the Formulas tab: Navigate to the 'Formulas' tab on the top ribbon and select 'Date & Time' to explore available functions.
3. Insert the adjustment formula: Click into your target cell and input your desired formula, such as =D6-CHOOSE(WEEKDAY(D6,2),0,0,0,0,0,1,2).
4. Apply and drag: Press Enter to see the adjusted Friday date, then drag the fill handle to apply it to your entire dataset.

100% format compatibility with Microsoft Excel (.xlsx) files.Built-in function library with detailed syntax prompts and formula tooltips.Completely free and lightweight suite for advanced data analysis.Seamlessly calculate dynamic due dates across multiple worksheets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my WEEKDAY formula return the wrong day number?

The WEEKDAY function uses a 'return_type' argument to determine how days are numbered. If you omit it, Sunday defaults to 1. Using WEEKDAY(date, 2) sets Monday as 1 and Sunday as 7, which is usually the easiest logic for weekend calculations.

Can I use these formulas to skip public holidays as well?

Yes. The WORKDAY function allows you to include an optional 'holidays' array as its third argument. Simply reference a range containing your holiday dates, such as =WORKDAY(A2, -1, Holidays!A2:A15), and it will shift to the prior valid working day.

What if I want to move a weekend due date forward to Monday instead of back to Friday?

To push the date forward to Monday, you can adjust the CHOOSE function values to add days (e.g., adding 2 for Saturday and 1 for Sunday), or simply use the WORKDAY function like this: =WORKDAY(A2-1, 1). This tells the system to find the next available working day.

Excel & Data Problems