Add days to date in Excel excluding weekends and holidays (4 ways)

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 details.

# 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 the 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:

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

Adding days to 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 7^th 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 14^th 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 7^th 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 15^th May 2019 as the return of this formula

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.

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 here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!