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:
=WORKDAY(start_day,days,[holidays])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:
=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
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:
=WORKDAY(C7, D7, $B$15:$B$18)- 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:
=WORKDAY.INTL(start_date, days, [weekend], [holidays])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.
| [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 |
Case 2.1 – Excluding Two-Day Weekends
Steps:
- Select cell E7 and use the formula:
=WORKDAY.INTL(C7, D7, 7)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:
=WORKDAY.INTL(C7, D7, 16)
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.
| [weekend] | String |
|---|---|
| Saturday, Sunday | 0000011 |
| Sunday, Monday | 1000001 |
| Saturday | 0000010 |
| Sunday | 0000001 |
| Friday | 0000100 |
| Tuesday, Friday | 0100100 |
| Friday, Saturday | 0000110 |
- Here’s the formula:
=WORKDAY.INTL(C7, D7, "0100100")
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:
=WORKDAY.INTL(C7, D7,7, $B$15:$B$18)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
Get FREE Advanced Excel Exercises with Solutions!
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
this is great. What do i do if I don’t know the start date yet?
Hello Deanna,
Thank you! We are glad to hear your appreciation. If you don’t know the start date yet, you can set up the formula to reference the cell where the start date will be entered later. This way, once you input the start date, the calculation will automatically update. You can set up your sheet and place all the required formulas then based on your input calculation will happen automatically.
Let us know if you need further help!
Regards
ExcelDemy