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

excel add days to date excluding weekends and holidays

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:

[weekend]value
Saturday, Sunday1
Sunday, Monday2
Monday, Tuesday3
Tuesday, Wednesday4
Wednesday, Thursday5
Thursday, Friday6
Friday, Saturday7
Saturday Only11
Sunday Only12
Monday Only13
Tuesday Only14
Wednesday Only15
Thursday Only16
Friday Only17

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)

add working days to date with default weekend and without holidays

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)

Adding working days to date considering holidays (default weekend Sat & Sun)

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

Adding working days to date without considering holidays (weekend Fri & Sat)

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

Adding working days to date considering holidays (weekend Fri & Sat)

  • 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

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.


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!

We will be happy to hear your thoughts

      Leave a reply