In this tutorial, we shall discuss how to add days to date in Excel excluding weekends and holidays.
I shall 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 me with the advancement of the article.
Excel functions for finding 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 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 you want to add to your current date (start_date)
- [holidays]: The list of holidays in an Excel range.
# WORKDAY.INTL Function 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.
Adding days to a date in Excel excluding weekends and holidays
Choose the method that best suits your job.
1) Adding working days to date without considering holidays (default weekend Sat & Sun)
My first Future Date 1 is showing the formula:
=WORKDAY(H6, H7)
A straightforward Excel formula.
- H6 is the start date
- H7 is the number of working days we want to add
- We get 7th May 2019 as the return of this formula
2) Adding working days to date considering holidays (default weekend Sat & Sun)
This is the formula I have used to calculate the date from the start date and excluding the holidays and default weekend days (Saturday and Sunday).
=WORKDAY(H6,H7,E7:E29)
- H6 is the start date
- H7 is the number of working days we want to add
- E7: E29 holds the holidays (you can add more holidays)
- We get 14th May 2019 as the return of this formula
3) Adding working days to date without considering holidays (weekend Fri & Sat)
This time, we are going to use the WORKDAY.INTL function as our weekend is different (Fri & Sat). For Friday and Saturday weekend, our value will be 7. To values, check the above table or Excel will auto-suggest you when you will input the function.
Formula Used: =WORKDAY.INTL(H6, H7, 7)
- H6 is the start date
- H7 is the number of working days we want to add
- Value 7 represents weekend Friday and Saturday
- We get 7th May 2019 as the return of this formula
4) Adding working days to date considering holidays (weekend Fri & Sat)
To add working days to a date considering holidays and weekend (Fri and Sat), I have used this formula:
=WORKDAY.INTL(H6,H7,7,E7:E29)
- H6 is the start date
- H7 is the number of working days we want to add
- Value 7 represents weekend Friday and Saturday
- E7: E29 holds the holidays (you can add more holidays)
- We get 15th May 2019 as the return of this formula
➥ Related: Calculate working days in Excel excluding weekends & holidays
Conclusion
So, these are my ways to add days to date excluding weekends and holidays. Do you know any better technique? Let us know in the comment section.
Further Readings
- How to Add Months to a Date in Excel (2 Ways)
- Add Days to Date Using Excel Formula
- How to Add 30 Days to a Date in Excel (7 Quick Methods)
- How to Add Dates in Excel Automatically
- Excel Add Months to Date (4 Easiest Examples)
- Add Weeks to A Date in Excel [4 Fast & Simple Methods with Template]
- How to Add/Subtract Years to a Date in Excel
- How to Add 7 Days to a Date in Excel (3 Methods)
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