WORKDAY.INTL Function – Google Sheets

The WORKDAY.INTL function calculates a workday a given number of days 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.

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

Thumbnail for the WORKDAY and WORKDAY.INTL function video tutorial
YouTube Video Tutorial on the WORKDAY and WORKDAY.INTL Functions

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.

Two examples of WORKDAY.INTL
Examples of the WORKDAY.INTL Function

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.

Data ready to be used in WORKDAY.INTL function
The Inputs for the WORKDAY.INTL Function

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.

Name box
Naming the Range

Now we can use an easier formula swapping out the B4:B13 with the named range.

WORKDAY.INTL formula using a named range
The Holidays in a 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 from the web.

Leave a Comment