The WORKDAY.INTL function calculates a workday a given number of workdays away from a date. You can optionally specify a list of holidays to exclude from the count and other weekends.
If your days, months, and years are in separate cells, use the DATE function to combine them. This function is different from the WORKDAY function as you can also specify other weekends.
Contents
Syntax
=WORKDAY.INTL(start_date,days,[weekend],[holidays])
start_date– The date from which to start counting the days.days– The number of days to add to the start_date[weekend]– Optional weekend specification.- Number method
- 1 – Saturday and Sunday [don’t need to specify]
- 2 – Sunday and Monday
- 3 – Monday and Tuesday
- 4 – Tuesday and Wednesday
- 5 – Wednesday and Thursday
- 6 – Thursday and Friday
- 7 – Friday and Saturday
- 11 – Sunday only
- 12 – Monday only
- 13 – Tuesday only
- 14 – Wednesday only
- 15 – Thursday only
- 16 – Friday only
- 17 – Saturday only
- String method – Each digit represents a day. The first digit is Monday.
- 0000011 – Saturday and Sunday
- 0100000 – Only Tuesday
[holidays]– Optional specification of which days to exclude from the workdays.
Video Tutorial
Related Functions
WEEKDAY – Returns the week number for a given date.
DAYS – Find the number of days between two dates.
NETWORKDAYS.INTL – Calculate the number of working days between two days and excludes specified days as weekends.
NETWORKDAYS – Calculate the number of working days between two days and exclude weekends.
WORKDAY – Like this function but with only Saturday and Sunday as the weekends.
Errors
#NUM – The inputs are numbers but are not valid dates. If you used the 35th day of November, â11/35/2018â, this error could happen.
#VALUE! – The inputs don’t convert to a number such as “The other day” or “Yester-yester-day.”
Examples
WORKDAY.INTL With Different Weekends and No Holidays
Let’s look at how the function calculates an end date with two weekend days per week versus one.
As you can see, the thirty workday period ends sooner with only one weekend day per week.
WORKDAY.INTL With Federal Holidays
Next, we will look at an example with several holidays.
From the list of holidays, Independence Day is the only one within 60 days of the 7/1/2022 Start date. The other holidays have no impact on the calculation. If you’re following along on the example sheet, you’ll see the end date change by one if you delete Independence Day from the holiday list.
âšī¸ If a holiday and a weekend are on the same day, only one day will be removed from the count.
Using a Named Range for the Holidays
At this point, the range for the holidays is B4:B13. But, if you wanted to reference the list from another sheet, you would need Sheet1!B4:B13 which is longer and harder to remember. Let’s make this syntax a bit cleaner with a named range. Highlight B4:B13 and type Holidays into the Name box and press Enter.
Now we can use an easier formula swapping out the B4:B13 with the named range.
The formula of =WORKDAY.INTL(B1,B2,B3,Holidays) is the completed formula to calculate the workday sixty days away from the start date.
Live WORKDAY.INTL Examples in Sheets
Go to this spreadsheet for examples of the WORKDAY.INTL function shown above that you can study and use anywhere you would like.
Notes
Consider using the IMPORTXML function to obtain a list of your local holidays online.