Adding days to a date in Excel excluding weekends means adding only the workdays to a given date excluding the weekends between those dates. Weekends can vary from one country to another, even from one company to another.
In this Excel tutorial, we’ll learn how to add days to a date in Excel excluding weekends.
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.
Adding days to date excluding weekends can be done in two ways: Using the WORKDAY function and using WORKDAY.INTL function. The WORKDAY function considers Saturday and Sunday as weekends by default. But we can assign a custom weekend parameter with the WORKDAY.INTL function. We will also show you how to exclude holidays along with weekends while adding days to a date in Excel.
Why Use WORKDAY Function While Adding Days to Date in Excel Excluding Weekends?
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:
=WORKDAY(start_day,days,[holidays])
The following image shows the difference between adding days to a date using a simple addition formula and using the WORKDAY function.
When we simply add 5 days to 12 October 2023, we get 17 October 2023 as expected. However, when we use the WORKDAY function, it only adds the working days excluding weekends. So, you can see, we added five days to the same date but the result is different.
1. Using WORKDAY Function to Add Days to a Date in Excel Excluding Weekends
In this part, we’ll use the WORKDAY function to add days to a date in Excel excluding the weekends. Moreover, we’ll show how to exclude both weekends and holidays while adding days to date in Excel.
1.1 Excluding Only Weekends
Here, we’ll use the WORKDAY function for adding days to a date excluding weekends.
Let’s see the dataset first. Here we have some products, order dates, and days needed to deliver them. We will show how to calculate the delivery date excluding the weekends.
So follow the steps below.
Steps:
- Select cell E7 => Type the formula:
=WORKDAY(C7, D7)
- 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
1.2 Excluding Weekends and Holidays
Here, we’ll use the WORKDAY function for adding days to a date excluding weekends and holidays.
In the below dataset, we have some products, order dates, and days needed to deliver them. Here, a list of holidays is also given. We have to find out the delivery date considering both weekends and holidays.
Therefore, learn the following steps.
Steps:
- Select cell E7 => Insert the formula:
=WORKDAY(C7, D7, $B$15:$B$18)
- Press Enter => Use Fill Handle to copy the formula in the cells below.
Read More: How to Add Weeks to a Date in Excel
2. Using Excel WORKDAY.INTL Function to Add Days to a Date Excluding Weekends
In this section, we’ll use the WORKDAY.INTL function to add days to a date excluding weekends. This function is available in Excel 2010 and later versions.
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 the main benefit is you can customize the weekends.
The syntax of this function is:
=WORKDAY.INTL(start_date, days, [weekend], [holidays])
Here, the weekend argument is optional. In this argument, we can use any value from the following list to customize the weekend. The list displays the corresponding values for different days of weekends.
[weekend] | Value |
---|---|
Saturday, Sunday | 1 |
Sunday, Monday | 2 |
Monday, Tuesday | 3 |
Tuesday, Wednesday | 4 |
Wednesday, Thursday | 5 |
Thursday, Friday | 6 |
Friday, Saturday | 7 |
Sunday Only | 11 |
Monday Only | 12 |
Tuesday Only | 13 |
Wednesday Only | 14 |
Thursday Only | 15 |
Friday Only | 16 |
Saturday Only | 17 |
2.1 Excluding Two-Day Weekends
In this part, we’ll apply the WORKDAY.INTL function to add days to a date excluding two-day weekends. Follow the steps below.
Steps:
- Select cell E7 => Write the formula:
=WORKDAY.INTL(C7, D7, 7)
Here, we use the weekend parameter 7 to consider Friday and Saturday as weekends.
- Press Enter => Use the Fill Handle to copy the formula to the cells below.
Read More: How to Add 30 Days to a Date in Excel
2.2 Excluding One-Day Weekend
In this part, we’ll use the WORKDAY.INTL function to add days excluding one day weekend. Suppose the weekend is only on Friday. In such a case, the weekend parameter to be used is 16. The formula will be:
=WORKDAY.INTL(C7, D7, 16)
2.3 Excluding Non-Consecutive Weekends
Some industries or companies might have non-consecutive weekends, for example, weekends on Friday and Tuesday. In those cases, we will use the WORKDAY.INTL function to add days to a date excluding those non-consecutive weekends. We will use a string as the weekend parameter for them.
Weekend String: 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. So, if the weekends are on Friday and Saturday, the accurate sequence will be: “0000110”. If the weekends are on Friday and Tuesday, the accurate sequence will be: “0100100”. The following table shows the corresponding strings for different weekends.
[weekend] | String |
---|---|
Saturday, Sunday | 0000011 |
Sunday, Monday | 1000001 |
Saturday | 0000010 |
Sunday | 0000001 |
Friday | 0000100 |
Tuesday, Friday | 0100100 |
Friday, Saturday | 0000110 |
The formula to exclude non-consecutive weekends: Tuesday and Friday, when adding days to date is:
=WORKDAY.INTL(C7, D7, "0100100")
2.4 Excluding Holidays & Weekends
In this part, we’ll use the WORKDAY.INTL function for adding days to a date excluding weekends and holidays. Learn the following steps.
Steps:
- Select cell E7 => Write the formula:
=WORKDAY.INTL(C7, D7,7, $B$15:$B$18)
Here, we use the weekend parameter 7 to consider Friday and Saturday as weekends.
- Press Enter => Use Fill Handle to copy the cells below.
Read More: How to Add Months to Date in Excel
Download Practice Workbook
This article has shown how to add days to a date in Excel excluding weekends using 2 functions: WORKDAY and WORKDAY.INTL. Use the latter one to assign a custom weekend parameter. Moreover, we showed how to add days excluding both weekends and holidays. Leave a comment for any further queries.
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 | Calculate Dates | Date-Time in Excel | Learn Excel
Great article. Continue posting your instructive articles.
Thanks.
Sir, 12th January, 2021.
Very nicely and clearly you have explained notes on Day and Dates.
I am sure people like me will definitely use and learn those tips.
Hoping to receive such useful tips in future too.
Once again thanking you.
I remain.
Kanhaiyalal Newaskar.
Kanhaiyalal, thanks for your wonderful feedback.
Best regards