Function Problems

Excel Formula to Return the Previous Working Date

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs an Excel formula to calculate the most recent previous business date, effectively excluding weekends and a custom list of company holidays, even if the starting input date is on a weekend.

Product: Spreadsheet
Device & OS: not provided
Scenario: Calculating accurate business deadlines, past working days for project management, or payroll dates while accounting for non-working days.
Observed behavior: Requires a reliable formula using the WORKDAY function that properly steps backward while skipping predefined holidays and standard weekend days.

Before you start

Ensure that your starting date cell and your list of custom holiday dates are formatted as valid Excel dates, not as plain text.

Solution 1Recommended

Use the WORKDAY Function with a Holiday Range

Use this method to find the most recent working date on or before a given date, skipping weekends and custom company holidays.

The WORKDAY function calculates a date a specific number of working days in the past or future. By using a clever adjustment (+1 to the start date and -1 to the days argument), the formula will return the starting date if it's already a workday, or it will roll back to the nearest previous working date if the start date falls on a weekend or holiday.

Select your target cell

Click on the cell where you want the previous working date to be displayed.

Enter the WORKDAY formula

Type the formula: =WORKDAY(C4+1, -1, SCHEDULES!$A$2:$A$19) into the cell. Replace 'C4' with your actual starting date cell, and 'SCHEDULES!$A$2:$A$19' with the range containing your holiday dates.

Format as Date

Press Enter to apply the formula. If the result shows as a plain number (like 44567), right-click the cell, select 'Format Cells', and choose 'Date' to display it correctly.

Absolute References for Holidays: It is highly recommended to use absolute references (like $A$2:$A$19) for your holiday list so the range doesn't shift if you copy the formula down to other rows.

Solution 2

Use the Basic WORKDAY Formula (Weekends Only)

Use this simplified formula if you only need to exclude standard weekends and do not have any specific company holidays to skip.

Work More Efficiently

Master Date Calculations with WPS Spreadsheet

WPS Spreadsheet provides full support for the WORKDAY function and hundreds of other advanced data calculation formulas. Easily calculate project deadlines, business days, and timelines with a robust, free spreadsheet tool.

1. Open WPS Spreadsheet: Launch the application and open your schedule or project planning workbook.
2. Insert the WORKDAY Formula: Select an empty cell and enter the WORKDAY formula just as you would in Excel.
3. Select the Holiday Range: Highlight your custom holiday dates easily using your mouse to add them to the formula.
4. Get Instant Results: Press Enter to instantly calculate the exact previous working date.

100% format and formula compatibility with Microsoft Excel.Built-in function syntax guides to prevent formula errors.Free, lightweight, and works seamlessly across multiple devices.Supports complex arrays and referenced sheets perfectly.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my WORKDAY formula return a #VALUE! error?

This error occurs when the starting date or one of the dates in the holiday range is formatted as text instead of a valid Excel date. Check the formatting of your cells and ensure they are recognized as dates.

How can I calculate previous working days if my company works on Saturdays?

You can use the WORKDAY.INTL function instead of WORKDAY. It allows you to specify custom weekend parameters (for example, setting only Sunday as the weekend) using a special weekend string or number.

Does the WORKDAY function count the start date as a working day?

No, the standard WORKDAY function begins counting from the day after (or before, if negative) the start date. That is why the formula =WORKDAY(C4+1, -1) is often used as a trick to evaluate the start date itself.

Excel & Data Problems