Excel WORKDAY.INTL Function – How To Use

The WORKDAY.INTL function is categorized as a DATE/TIME function in Excel and is an extension of the WORKDAY function that offers more customizability. In addition to allowing you to calculate N working days into the future or past like the WORKDAY function, it also allows you to set custom weekends.

The function has broad applicability in the business and financial world. It allows calculating productivity by day, computing metrics related to collections from debtors, or automatically compute due dates for invoices.

Excel WORKDAY.INTL Function

Syntax

The syntax of the WORKDAY.INTL function is as follows:

=WORKDAY.INTL(start_date, days, [weekend], [holidays])

Arguments:

'start_date' – This is a required argument where you must enter the date from which you want to begin your calculation.
'days' – This is a required argument where you may insert the number of working days you want to calculate into the future (positive value) or past (negative value).
'weekend' – This is an optional argument where you need to enter a code (see table below) that represents the day(s) that are weekend days. Alternatively, you may enter a binary text string using 0s and 1s to instruct Excel as to which days must be counted as weekend days. By default, the weekend days are assumed to be Saturday and Sunday.
'holidays' – This is an optional argument where you may enter a cell range that contains a list of holidays, which may or may not be in chronological order. The dates must be valid serial numbers that Excel recognizes as valid dates.

Weekend Codes and Description

Weekend CodeWeekend Days
1 (Default)Saturday, Sunday
2Sunday, Monday
3Monday, Tuesday
4Tuesday, Wednesday
5Wednesday, Thursday
6Thursday, Friday
7Friday, Saturday
11Sunday only
12Monday only
13Tuesday only
14Wednesday only
15Thursday only
16Friday only
17Saturday only

Important Characteristics of the WORKDAY.INTL function

  • The WORKDAY.INTL function calculates N working days into the future or the past, taking into consideration the data provided in the [weekend] and [holidays]
  • The function helps you calculate delivery dates, due dates, and other similar calculations where you need to calculate working days.
  • Using a positive value in the days argument will instruct the function to calculate working days in the future, while a negative value will instruct the function to calculate working days in the past.
  • The WORKDAY.INTL function is an extension of the WORKDAY function because it allows setting customized weekends, which the WORKDAY function doesn't.
  • The date entered in the start_date is excluded from the calculation of the WORKDAY.INTL function.
  • By default, the WORKDAY.INTL function assumes Saturdays and Sundays as weekend days.
  • If the date entered in any of the three (start_date, days, or [holidays]) arguments is invalid, the function returns a #NUM! error.
  • If the value entered in the [weekend] argument is invalid, the function returns a #VALUE! error.

Examples

Let's try to see some examples of the WORKDAY.INTL function.

Example 1 – Plain Vanilla Formula for the WORKDAY.INTL Function

Let's start with a simple, no-fuss version of the WORKDAY.INTL function. We will ignore [holidays] and [weekend] arguments for the time being. The formula we'll use then is as follows:

=WORKDAY.INTL(A2,7)

Excel-WORKDAY.INTL-Function-Example-01

Notice how calculating 7 working days from December 25 takes us to January 3 instead of January 1. This is because 2 days: December 29 and 30 are a Saturday and Sunday, respectively. The function doesn't count them as working days, and they are therefore excluded from the computation.

Example 2 – Using the Optional Arguments

Alright, let's fully leverage the potential of the WORKDAY.INTL function and use its optional arguments, because that's where its most distinct feature lies. Say your company decides to treat Thursdays and Fridays as weekend days instead of Saturdays. Plus, you'll also give your employees the last and the first day of the year off since they have worked so hard throughout the year to help your company grow.

In this case, calculating 7 days would require using the following formula:

=WORKDAY.INTL(A2,7,6,E2:E3)

Excel-WORKDAY.INTL-Function-Example-02

The 6 in the formula's third argument is the weekend code for setting Thursdays and Fridays as weekends (see table at the top), and the E2:E3 range contains the 2 dates that we want to be counted as holidays.

Notice how using this formula changes the output to January 7 (i.e., 13 days after December 25).

This is because even though the formula is still counting 7 working days, there are 4 weekend days between December 25 and January 7: December 27 and January 3 (Thursdays), December 28, and January 4 (Fridays). Plus, there are 2 holidays: December 31 and January 1. Out of the total 13 days, 6 days are off days, which leaves us with 7 working days.

Pretty cool, isn't it? But… what if your CEO is really in a mood one day and decides to give Wednesdays and Saturdays off? Well, let's remedy that.

Example 3 – Binary Text String for Custom Weekends in WORKDAY.INTL Function

What makes the WORKDAY.INTL function even more powerful is that it allows you to create a custom work week where you can choose any number of days as weekends, regardless of whether they are consecutive or not.

For instance, let's continue using the data from previous examples (without adding any holidays) and see how the output changes if we set Wednesdays and Saturdays as weekends. We'll construct the formula pretty much the same except for the [weekend] argument, where we'll use a binary text string, like so:

=WORKDAY.INTL(A2,7,"0010010")

Excel-WORKDAY.INTL-Function-Example-03

This time around, the output changes to January 4. Well, there are 10 days in total between December 25 and January 4. However, there are two Wednesdays (December 26 and January 2) and 1 Saturday (December 29). Taking these three days out of 10 days gives us … you guessed it — 7 working days!

Example 4 – Acceptable Inputs for [holidays] Argument

The [holidays] argument requires information about the dates that are to be considered as holidays. However, if your calculation only has a few holidays in the timeframe, you may not want to go through the hassle of creating a separate column and listing out the holidays.

Fortunately, there is a simpler way to accomplish this. The [holidays] argument also accepts validly formatted text string dates or serial numbers. For instance, if we want to instruct Excel to treat December 31 and January 1 as holidays, we could use either of the following formulas:

=WORKDAY.INTL(A2,7,6,{"12/31/2001","01/01/2002"})

OR

=WORKDAY.INTL(A3,7,6,{37256,37257})

Excel-WORKDAY.INTL-Function-Example-04

Using either of these formulas will return the same output as the previous example (i.e., January 7). This is because we haven't changed anything except for the method of supplying the information to the [holidays] argument.

Note: Excel internally stores dates as serial numbers. There is no magical formula that Excel uses to assign serial numbers to dates, though. The serial numbers are assigned such that January 1, 1900 is considered as 1, and thereafter the numbers increment with each day. Based on the same logic, Dec 31, 2001, corresponds to 37,256 because it is 37,255 days after January 1, 1900.

Recommended Reading – Excel NETWORKDAYS.INTL Function

WORKDAY Function vs WORKDAY.INTL Function

The WORKDAY function has all the arguments that the WORKDAY.INTL function has, except for the [weekend] argument. For this reason, WORKDAY.INTL is a more robust version of the relatively simpler WORKDAY function.

As is obvious, the WORKDAY.INTL function's primary value comes from its ability to set custom weekends. Regardless of what weekend structure you want to use, you'll be able to customize it in the formula and calculate the number of working days into the future or the past.

That brings our WORKDAY.INTL saga to an end. Hopefully, this tutorial will help you gain an edge over your colleagues as you run financial simulations or prepare efficient spreadsheets and get you noticed by the boss. While you keep your Excel thinking hat on, we'll pull some more tutorials out of the bag. When you've mastered the WORKDAY.INTL function, you know where to find us.

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.