Calculation Issues

How to Calculate Due Dates Excluding Weekends and Holidays in Excel

Tauseeq Magsi Oct 21, 2026 868 views

Question details

The user needs to compute a specific due date by adding consecutive calendar days to a start date, but ensure the final date rolls forward to the next working day if it initially lands on a weekend or a holiday.

How to Calculate Due Dates Excluding Weekends and Holidays in Excel

Product: Excel
Device & OS: not provided
Scenario: Determining precise project deadlines, contract expirations, or payment due dates where the final submission day cannot be on a non-working day.
Observed behavior: The user wants a formula that adds calendar days natively but triggers a rollover mechanism to find the next valid business day, referencing a custom list of company or national holidays.

Before you start

Prepare a dedicated column or range in your spreadsheet to list all recognized company or national holidays, and verify that all your acceptance dates and holiday dates are stored in a valid Date format, rather than as text.

Solution 1Recommended

Apply the WORKDAY.INTL Formula for Rollover Due Dates

Use a combination of simple calendar day addition and the WORKDAY.INTL function to accurately push a weekend or holiday deadline to the next available business day.

The WORKDAY.INTL function is highly flexible because it allows you to define exactly which days of the week are considered weekends using a simple 7-character binary string. By initially adding the consecutive days and then using WORKDAY.INTL to add a single working day, we can accurately roll over any non-working dates without skipping valid days.

Set up your source data

Place your start or acceptance date in cell A2 and the number of consecutive calendar days you need to add in cell B2.

Define your holiday list

Enter your specific holiday dates in a separate range, such as cells H2 through H20. Use absolute references ($H$2:$H$20) so the formula won't shift when copied down.

Enter the formula

Select the target cell for the final due date, click the formula bar, and type: =WORKDAY.INTL(A2+B2-1, 1, "0000011", $H$2:$H$20) and press Enter.

Adjust the starting day convention

If your counting convention requires the start date to be day zero rather than day one, simply remove the '-1' from the formula so the first parameter reads A2+B2.

Apply the WORKDAY.INTL Formula for Rollover Due Dates

Formatting Output: If the formula result displays as a 5-digit number (e.g., 44560), right-click the cell, select 'Format Cells', and choose 'Date' to display the actual due date.

Powerful Spreadsheet Features

Calculate Complex Deadlines Seamlessly with WPS Spreadsheet

WPS Spreadsheet fully supports advanced date and time functions, including WORKDAY.INTL, allowing you to manage project timelines, calculate valid due dates, and track holiday schedules effortlessly.

1. Open WPS Spreadsheet: Launch WPS Office and open your project timeline or task tracking spreadsheet.
2. Input Dates and Holidays: Create your list of start dates, task durations, and holiday dates in your worksheet.
3. Apply Formula: Select the target cell and type =WORKDAY.INTL() to utilize the date rollover calculation exactly as you would in Microsoft Excel.
4. Format Result: Right-click the result cell, select 'Format Cells', and choose 'Date' from the Number tab to display the calculated deadline correctly.

Fully compatible with Microsoft Excel date and time formulasBuilt-in cell formatting tools for quick calendar date displaysFree, lightweight, and fast alternative for daily data analysis

microsoft office alternative - wps office

Frequently Asked Questions

What does the "0000011" string mean in the WORKDAY.INTL formula?

The 7-character string represents the days of the week, starting from Monday to Sunday. A '0' indicates a standard workday, and a '1' indicates a non-workday (weekend). Therefore, "0000011" sets Saturday and Sunday as the weekend.

Can I use the standard WORKDAY function instead of WORKDAY.INTL?

Yes, if your workweek always has Saturday and Sunday off, the standard WORKDAY function works. However, WORKDAY.INTL is highly recommended if you have non-standard weekends or need to use the binary string method for greater schedule flexibility.

Why does my calculated due date display as a 40000-level number?

Excel and WPS Spreadsheet store dates as sequential serial numbers for calculation purposes. If you see a number like 44123, select the cell, navigate to the Home tab, and change the Number Format dropdown from 'General' to 'Short Date'.

Excel & Data Problems