How to Calculate the Previous Business Day Excluding Holidays in Excel
Question details
The user needs a formula to calculate the previous working day, ensuring that standard weekends and a specific list of holidays are automatically skipped.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Setting up a dynamic date formula for business reports or tracking that requires knowing the exact last working day prior to the current date.
- Observed behavior
- Standard functions like WEEKDAY can identify weekend days but do not automatically exclude custom non-working days like US holidays.
Compile a list of upcoming US holiday dates in a blank column of your spreadsheet. The formula will need to reference this specific list to know which dates to skip.
Calculate Previous Business Day using the WORKDAY Function
Use the WORKDAY function to subtract a day from the current date while instructing the spreadsheet to ignore weekends and your specified holiday dates.
The WORKDAY function takes a start date, the number of days to add or subtract, and an optional range of holiday dates. By using a negative value (-1), it steps backward to the most recent working day.
Enter all US holiday dates into a blank column in your worksheet, for example, cells A2 through A10.
Select the cells containing your holiday dates, click the Name Box located to the left of the formula bar, type 'HOLIDAY', and press Enter.
Select the cell where you want the previous business day to appear and enter the formula: =WORKDAY(TODAY(),-1,HOLIDAY).
If the formula result appears as a large serial number, right-click the cell, select 'Format Cells', and choose your preferred Date format.
Referencing a Direct Cell Range for Holidays
If you prefer not to use Named Ranges, you can reference the cell addresses containing your holidays directly within the formula.
Calculate Business Dates Easily with WPS Spreadsheet
WPS Spreadsheet fully supports the WORKDAY function and seamlessly handles complex date calculations, making it a perfect tool for tracking business days and holidays without any extra add-ins.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in the WPS Spreadsheet application.
- 2. Input Holiday Dates: Enter your holiday dates into a column and highlight them to define a custom name in the Name Box.
- 3. Use the Formula: Type =WORKDAY(TODAY(), -1, [YourHolidayRange]) into any empty cell to accurately calculate the previous business day.
Frequently Asked Questions
Can I use a dynamic table for my holiday list?
Yes, converting your holiday list into a Table (Ctrl+T) and referencing the table column in your WORKDAY formula ensures that any new holidays added later are automatically included in the calculation without updating the formula.
What if my company's weekends are not Saturday and Sunday?
If your non-working days differ from the standard weekend, use the WORKDAY.INTL function instead. It allows you to specify exactly which days of the week should be treated as the weekend using a custom weekend parameter.
Why is my formula returning a 5-digit number instead of a date?
Spreadsheets store dates as sequential serial numbers. To display it correctly as a date, select the cell, navigate to the Home tab, and change the Number Format dropdown from 'General' to 'Short Date'.
Can I subtract more than one business day at a time?
Absolutely. You can change the '-1' in the formula to the exact number of business days you want to subtract. For example, use '-3' to find the date exactly three business days ago.
