How to Add Days to a Date in Excel Excluding Weekends (4 Ways)

In the following overview image, we have added 6 days to an Order Date of 12 October 2023 using two different functions. As the two functions consider different weekends, the resulting Delivery Dates are different.

Why Use the WORKDAY Function While Adding Days to a Date?

The WORKDAY function returns a future or past date by excluding weekends (Saturdays and Sundays) and optionally excluding the specified holidays from the calculation. This function considers Saturday and Sunday as weekends by default.

The syntax of the function is:

The following image shows the difference between a simple addition formula and the WORKDAY function. When we simply add 5 days to 12 October 2023, we get 17 October 2023 as expected. When we use the WORKDAY function, it only adds the working days excluding weekends.

Method 1 – Using the WORKDAY Function to Add Days to a Date in Excel Excluding Weekends

Case 1.1 – Excluding Only Weekends

We have some products, order dates, and days needed to deliver them.

Steps:

• Select cell E7 =and insert the following formula:

• Press Enter.

• Use the Fill Handle to copy the formula in other cells below. It’ll return all the delivery dates.

Read More: How Do I Add 7 Days to a Date in Excel

Case 1.2 – Excluding Weekends and Holidays

• We’ve added a list of holidays in the range B15:B18.

• Select cell E7 and insert the formula:

• Press Enter and use the Fill Handle to copy the formula in the cells below.

Read More: How to Add Weeks to a Date in Excel

Method 2 – Using the Excel WORKDAY.INTL Function to Add Days to a Date Excluding Weekends

The WORKDAY.INTL function returns a future or past date by excluding weekends. By default, it considers Saturday and Sunday as weekends like the WORKDAY function, but you can customize these weekends. This function is available in Excel 2010 and later versions.

The syntax of this function is:

The weekend argument is optional. You can use any value from the following list to customize the weekend. Alternatively, you can input a seven-character string of 1s and 0s to indicate weekends and working days. For example, 0000011 is a Saturday-Sunday weekend.

Case 2.1 – Excluding Two-Day Weekends

Steps:

• Select cell E7 and use the formula:

We used the weekend parameter 7 to consider Friday and Saturday as weekends.

• Press Enter and use the Fill Handle to copy the formula to the cells below.

Read More: How to Add 30 Days to a Date in Excel

Case 2.2 – Excluding a One-Day Weekend

• Suppose the weekend is only on Friday. The weekend parameter to be used is 16.
• The formula will be:

Case 2.3 – Excluding Non-Consecutive Weekends

A string of seven 1s and 0s represents seven days of the week in the weekend parameter of the function. Here, 1 means an off day and 0 means a workday. The string starts with Monday. If the weekends are on Friday and Saturday, the sequence will be “0000110”.  If the weekends are on Friday and Tuesday, it’s “0100100”. The following table shows some strings for different weekends.

• Here’s the formula:

Case 2.4 – Excluding Holidays and Weekends

Steps:

• We put the holiday table in the range B15:B18.
• Select cell E7 and use this formula:

The weekend parameter 7 considers Friday and Saturday as weekends.

• Press Enter and use the Fill Handle to copy to the cells below.

Read More: How to Add Months to Date in Excel

Download the Practice Workbook

Further Readings

• How to Add 3 Months to a Date in Excel
• How to Add 6 Months to a Date in Excel
• How to Add Years to a Date in Excel
• How to Add 2 Years to a Date in Excel
• How to Add 3 Years to a Date in Excel
• How to Create a Formula in Excel to Change Date by 1 Year

<< Go Back to Adding Days to Date | Learn Excel