Calculate a Future Workday Date Excluding Holidays in WPS Office Excel
Question details
The user needs to calculate a date two months after a start date while skipping weekends and a holiday list in WPS Office Excel.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is working with date-based planning in a spreadsheet and needs a future working date for deadlines, follow-ups, or scheduling.
- Observed behavior
- The target two-month date may fall on a weekend or listed holiday, so it must be advanced to the next valid workday.
Make sure the start date in H10 is a real date value and that the holiday range Q7:Q45 contains actual dates in a continuous range. If your workbook stores dates as text, the WORKDAY and EDATE formula will not calculate correctly.
Use WORKDAY with EDATE to Return the Next Workday After Two Months
Use this formula when you need the first working day on or after the two-month date, excluding standard weekends and a specific holiday range.
The formula subtracts one day from the EDATE result and then advances one working day. This makes the result fall on the first valid workday after the two-month anniversary, never on a weekend or a listed holiday.
Open WPS Office Spreadsheets and select the worksheet that contains the start date in cell H10 and the holiday list in cells Q7:Q45.
Click the cell where you want the future workday date to appear, enter =IF(H10="","",WORKDAY(EDATE(H10,2)-1,1,Q7:Q45)), and press Enter.
If the result appears as a serial number, select the cell, click Number Format on the Home tab, and choose Short Date or Long Date.
Use WORKDAY.INTL for a Custom Weekend or Non-Standard Workdays
Use this variation when weekends are not Saturday/Sunday or when you want to specify which weekdays are workdays.
Calculate Future Workdays in WPS Office Spreadsheets
Use WPS Office Spreadsheets to calculate project deadlines and follow-up dates with Excel-compatible date functions, including WORKDAY, EDATE, and WORKDAY.INTL.
- 1. Download and install WPS Office: Go to the official WPS Office website, download WPS Office, and install it on your computer.
- 2. Open your existing Excel workbook: Launch WPS Office Spreadsheets and open the .xlsx or .xls workbook that contains the start dates and holiday list.
- 3. Enter the workday formula: Click the destination cell, type =IF(H10="","",WORKDAY(EDATE(H10,2)-1,1,Q7:Q45)), and press Enter to calculate the next working day after two months.
- 4. Apply formatting and copy as needed: Format the result as a Short Date from the Number Format menu on the Home tab, then use the fill handle to copy the formula to other rows.
Frequently Asked Questions
Why does the formula use EDATE(H10,2)-1 before WORKDAY?
EDATE(H10,2) returns the exact date two months after the start date. Subtracting 1 day and then adding 1 workday ensures the formula returns the first working day on or after that date, so it skips weekends and any holiday in the specified range.
Can this formula be used in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports Excel-compatible functions such as WORKDAY, EDATE, WORKDAY.INTL, and IF, so the same formula works without changing workbook data.
How do I avoid changing the holiday range when copying the formula down?
Use an absolute range for the holidays, for example $Q$7:$Q$45. In the result cell, enter =IF(H10="","",WORKDAY(EDATE(H10,2)-1,1,$Q$7:$Q$45)) and drag the fill handle so only H10 updates for each row.
What should I do if the formula returns a number instead of a date?
The number is the date serial value. Select the cell, go to the Home tab, open Number Format, and choose Short Date or Long Date to display it as a normal date.
