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

Get FREE Advanced Excel Exercises with Solutions!

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.

add days to a date in excel excluding weekends

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.

workday function vs simple formula in adding days to a date


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.

dataset of product, order date, days needed

So follow the steps below.

Steps:

  • Select cell E7 => Type the formula:
=WORKDAY(C7, D7)
  • Press Enter.

using workday function to add days to a date excluding weekends in excel

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

using fill handle

Note: The WORKDAY function returns the serial number of a date. So to convert it to the date format, select the range => go to the Number section of the Home tab => and choose either Short Date or Long Date from the Number Format drop-down.

choosing date format

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.

dataset of product, dates 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.

using workday.intl function to add days to a date excluding weekends in excel

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.

using workday.intl function to add days to a date excluding weekends in excel

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)

using workday.intl function to add days to a date excluding a weekend in excel


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")

using workday.intl function with weekend string to add days to a date excluding weekends in excel


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.

using workday.intl function to add days to a date excluding weekends and holidays

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


<< Go Back to Adding Days to Date | Calculate Dates | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

3 Comments
  1. Great article. Continue posting your instructive articles.
    Thanks.

  2. Reply
    Kanhaiyalal Newaskar. Jan 12, 2021 at 11:22 AM

    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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo