How to return the previous working or business day in Excel?
In Excel daily work, you may often need to determine the last working or business day before a particular date—for example, to track payment deadlines, project milestones, or prepare business reports. Typically, business days are Monday through Friday, while weekends (Saturday and Sunday) and holidays are not counted. Finding the previous working day can be tricky when holidays and custom weekends are involved, but Excel provides several practical approaches for these needs. This article will introduce a straightforward formula-based method, and also provide VBA code for more flexible or advanced scenarios where you may need to customize weekends or manage dynamic holiday lists.
Return the previous working or business day with formula in Excel
Return the previous working or business day with formula in Excel
If your business routine only needs to skip Saturday, Sunday, and a fixed set of holidays, using Excel formulas is the simplest and most stable option. As shown in the screenshot below, suppose the range F2:F4 contains your holiday dates. You can use a formula to calculate the previous working (or business) day, skipping both the weekends and the provided holidays, based on either today or any specific date.
1. Click the cell where you want to display the previous working day (for example, cell B2). Type the following formula into the Formula Bar:
=WORKDAY(TODAY(),-1,F2:F4)2. Press Enter to apply the formula. If the result displays as a serial number instead of a formatted date, change the cell's format to Date as below screenshot shown. 
Notes:
- In this formula, TODAY() provides the current date as the starting point. If you want the previous working day for a date in another cell (for example, cell A2), replace TODAY() with A2 (like =WORKDAY(A2,-1,F2:F4)).
- Holidays should be listed as valid Excel date values (not text) in cells F2:F4. It’s fine if your holiday list is longer—just adjust the formula’s range. If you don’t want to exclude any holidays, you can omit the third argument entirely.
- By default, the WORKDAY formula treats Saturday and Sunday as weekends.
- If the referenced date is itself a non-working day (weekend/holiday), the formula still finds the working day before it.
- If there is an error or blank result, check that all referenced dates are valid, and that your holiday list does not contain non-date values.
This formula-based solution is ideal for users needing quick, reliable calculations within standard workweek rules and a static list of holidays. It is simple to set up, does not require enabling macros, and works in all recent versions of Excel.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in