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

Get FREE Advanced Excel Exercises with Solutions!

If you want to know about any project estimation date or end date excluding weekends & holidays you are in right place. In this tutorial, I will discuss how to add days to date in Excel excluding weekends and holidays. Here, I will show several methods. Use that method that best suits your requirement.

Download Excel File

Download the Excel file that I have used to make this tutorial and follow along with me with the advancement of the article.


Excel Functions to Find Working Days

You can use two functions:

  • WORKDAY: It is mainly for USA people or people who have weekends on Saturday and Sunday.
  • WORKDAY.INTL: This is a universal Excel function for calculating workdays.

Let’s know the functions in detail.

# WORKDAY Function in Excel

The syntax of WORKDAY Function:

WORKDAY(start_date, days, [holidays])

Here,

  • start_date: The date from where you want to calculate your workday
  • days: How many working days do you want to add to your current date (start_date)
  • [holidays]: The list of holidays in an Excel range.

# WORKDAY.INTL Function in Excel

The syntax of WORKDAY.INTL Function:

WORKDAY(start_date, days, [weekend], [holidays])

This function has an extra parameter. It is [weekend].

Different countries have different days for their weekends. Suppose, in my country (Bangladesh), we enjoy our weekend on Friday and Saturday.

So, I shall use 7 as the weekend value for this function.

Here is a list of 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
Saturday Only 11
Sunday Only 12
Monday Only 13
Tuesday Only 14
Wednesday Only 15
Thursday Only 16
Friday Only 17

I hope you understand how the functions work. Let’s now go to our main discussion.


4 Ways to Add Days to a Date in Excel Excluding Weekends and Holidays

Here, I’m going to use sample dates of Holidays to add dates excluding holidays and weekends.

how to add days to a date in excel excluding weekends


1. Using Excel WORKDAY Function to Add Working Days to Date Excluding Weekends

Here, I will use the WORKDAY function to add days to date excluding weekends. Let me show you the procedure.

Steps:

  • To begin with, select cell C8 then type the following formula.
=WORKDAY(C4,C5)

add days to a date in excel excluding weekends

Formula Breakdown

  • C4 is the start_date.
  • C5 is the number of working days we want to add.
  • Here, the default weekend is Saturday & Sunday.

  • Now, press ENTER to get the result. Here, I got 7th May 2019 as the return of this formula

Read More: How to Calculate Working Days Excluding Sundays in Excel


2. Adding Working Days to Date Excluding Holidays & Weekends in Excel

Let me show you how you can use a formula to calculate the date from the start date and excluding the holidays and default weekend days (Saturday and Sunday).

Steps:

  • At first, select cell D8 then type the following formula.
=WORKDAY(C4,C5,B8:B30)

adding days to a date in excel excluding weekends and holidays

Formula Breakdown

  • C4 is the start_date
  • C5 is the number of working days we want to add
  • B8: B30 holds the holidays (you can add more holidays)

  • Now, press ENTER to get the result. Here, I got 14th May 2019 as the return of this formula.

Read More: How to Add 30 Days to a Date in Excel (7 Quick Methods)


Similar Readings


3. Use of Excel WORKDAY.INTL Function to Add Days to Date Excluding Weekends

This time, I’m going to use the WORKDAY.INTL function as our weekend is different (Fri & Sat). For Friday and Saturday weekends, our value will be 7. To values, check the above table or Excel will auto-suggest you when you will input the function.

Steps:

  • At first, select cell C8 then type the following formula.
=WORKDAY.INTL(C4, C5, 7)

Formula Breakdown

  • C4 is the start_date
  • C5 is the number of working days we want to add
  • Value 7 represents weekend Friday and Saturday

  • Press ENTER to get the result. Here, I got 7th May 2019 as the return of this formula.

Read More: How Do I Add 7 Days to a Date in Excel (5 Methods)


4. Adding Days to Date Excluding Holidays & Weekend in Excel

Here, to add working days to a date considering holidays and weekends (Fri and Sat) I’m going to use the formula given below.

Steps:

  • Firstly, select cell D8 then type the following formula.
=WORKDAY.INTL(C4, C5, 7,B8:B30)

Formula Breakdown

  • C4 is the start_date
  • C5 is the number of working days we want to add
  • Value 7 represents weekend Friday and Saturday
  • B8:B30 holds the holidays (you can add more holidays)

  • Now, press ENTER to get the result. Here, I got 15th May 2019 as the return of this formula.

Read More: How to Calculate Working Days in Excel Excluding Weekends and Holidays


Practice Section

You can practice the explained methods in the practice section given in the dataset uploaded above. You can use the latest version of Microsoft Excel.

Practice to add days to a date in excel excluding weekends and holidays


Conclusion

So, these are my ways to add days to date excluding weekends and holidays in Excel. Do you know any better technique? Let us know in the comment section.


Further Readings

Khan Muhammad Kawser

Khan Muhammad Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

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