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.
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)
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)
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
- How to Calculate Number of Days Between Two Dates in Excel
- Calculate Years and Months between Two Dates in Excel (6 Approaches)
- How to Calculate Working Capital Days in Excel (with Easy Steps)
- [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
- How to Count Days from Date to Today Automatically Using Excel Formula
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.
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.
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.
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.
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