How to Exclude Holidays from Weekday Calculations in Excel
Question details
The user needs a formula to adjust dates to the nearest working day while properly skipping both weekends and specified public holidays.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating project deadlines, delivery schedules, or working day adjustments where holidays must be factored out.
- Observed behavior
- The current formula only adjusts dates to the closest Monday or Friday but fails to account for holidays, resulting in calculated dates falling on non-working holiday dates.
Compile a list of specific holiday dates in a separate column or sheet to use as a reference range in your calculations, and ensure they are formatted as valid dates.
Use the WORKDAY Function to Exclude Holidays
The WORKDAY function calculates a date a specified number of working days ahead or behind, automatically excluding weekends and an optional custom list of holidays.
By default, the WORKDAY function considers Saturday and Sunday as weekends. Providing a range of cells containing holiday dates allows the function to skip those specific days as well.
Enter all holiday dates in a specific continuous range of cells (for example, A1:A10) and verify they are formatted as valid dates, not text.
Click on the cell where you want the calculated adjusted date to appear (for example, Z10).
Type the formula =WORKDAY(X10, Y10, A1:A10) into the formula bar. In this example, X10 is your start date, Y10 is the number of days to offset, and A1:A10 is the range containing your holidays.
Press Enter to apply the formula. If you are calculating a backwards offset to find a previous working Friday, ensure your offset value (Y10) is a negative number or calculated correctly.
Easily Calculate Workdays and Exclude Holidays with WPS Spreadsheet
WPS Spreadsheet fully supports advanced date and time functions, including WORKDAY and WORKDAY.INTL. You can seamlessly manage project timelines and automate deadline calculations.
- 1. Open your document: Launch WPS Spreadsheet and open your schedule or project tracker.
- 2. List custom holidays: Create a dedicated column to list your custom holiday dates.
- 3. Enter the formula: Select the target cell and type =WORKDAY(start_date, days, holiday_range).
- 4. Apply and format: Press Enter to calculate the exact working day, and apply standard Date formatting from the Home tab.
Frequently Asked Questions
Can I use the WORKDAY function if my weekends are not Saturday and Sunday?
Yes. If your weekends fall on different days (e.g., Friday and Saturday), use the WORKDAY.INTL function instead. It includes an additional argument that lets you specify exactly which days of the week are considered weekends.
How do I find the previous working day if a date falls on a holiday?
You can use the WORKDAY function with a 0 or -1 offset depending on your exact logic. To simply find the previous working day from a specific holiday date in A2, you can use =WORKDAY(A2, -1, H1:H10).
Why does my WORKDAY formula return a #VALUE! error?
A #VALUE! error usually occurs if the start date, offset days, or holiday range contains text that cannot be recognized as valid dates or numbers. Ensure all referenced cells are formatted as Dates or General numbers.
