How to Use WORKDAY.INTL Function in Excel (3 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, WORKDAY.INTL function is used to find the next working day while taking into consideration all weekends and holidays. In this article, you’ll get to learn how to use this function efficiently in Excel.

The below screenshot is an overview of the article which represents an application of the WORKDAY.INTL function in Excel.

overview image of how to use WORKDAY.INTL function

You’ll learn more about the dataset, function parameters, and the appropriate use of the function to find the next working date in the following sections of this article.


Introduction to the WORKDAY.INTL Function

workday.intl function introduction in excel

Function Objective

The WORKDAY.INTL function returns the serial number of the date before or after a specified number of weekdays with custom weekend parameters.

Syntax

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

Arguments Explanation

Argument Compulsory/Optional Explanation
start_date Compulsory Starting date as the proper date format.
days Compulsory The number of days before or after the starting date. Use (-) for days before & (+) for days after.
[weekend] Optional Selected weekend(s) from the weekend’s index or serial number.
[holidays] Optional A cell or a range of cells containing holidays as the date format.

Weekend’s Index

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

Return Parameter

Date code or the serial number of a date stored in Excel based on the input criteria.


How to Use WORKDAY.INTL Function in Excel: 3 Suitable Examples

In this section, we will demonstrate 3 basic examples to show the use of the WORKDAY.INTL function. This section provides extensive details on these examples. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Determine Workday with Default Weekends and Excluding Holidays

Here, we will illustrate how to use the WORKDAY.INTL function to determine the past or next workday, excluding weekends and holidays, in Excel. To do the task, you have to follow the following procedure.

  • First of all, type the following formula in cell D5. (Keep in mind that when using this formula, the serial numbers must be formatted as dates).
=WORKDAY.INTL(B5,C5)

In this argument, B5 denotes the starting date and C5 denotes the total days.

The weekend parameter, which is optional, specifies which day(s) of the week should be weekends. Automatically, WORKDAY.INTL will consider Saturday and Sunday as weekends.

  • Next, press Enter.
  • Therefore, you will get the following workday.

How to use WORKDAY.INTL function

  • Then, drag the Fill Handle icon to fill the other cells with the formulas.
  • Therefore, you will get the following workdays, except weekends and holidays.

Drag the fill handle icon


2. Determine Workday with Custom Weekends and Excluding Holidays

Here, we’ll show you how to use Excel’s WORKDAY.INTL function to find the past or upcoming workday, including any weekends without holidays. To do the task, you have to follow the following procedure.

  • First of all, type the following formula in cell D5. (Keep in mind that when using this formula, the serial numbers must be formatted as dates.)
=WORKDAY.INTL(B5,C5,7)
  • In this argument, B5 denotes the starting date and C5 denotes the total days. 7 is the weekend index of Friday-Saturday that you have to select from the drop-down while moving to the 3rd argument input.
  • The non-working days can be specified using the optional argument holidays. Holidays should be specified as a range of dates that are valid in Excel.
  • Next, press Enter.
  • Therefore, you will get the following workday.

determine workday with custom weekends

  • Then, drag the Fill Handle icon to fill the other cells with the formulas.
  • Therefore, you will get the following upcoming workdays, including any weekends without holidays.

drag the fill handle icon


 

3. Determine Workday Including Weekend and Holidays

We’ll show you how to use Excel’s WORKDAY.INTL function to find the previous or upcoming workday, including any weekends and holidays. To complete the task, you must follow the steps below.

  • First of all, type the following formula in cell D5. (Keep in mind that when using this formula, the serial numbers must be formatted as dates)
=WORKDAY.INTL(B5,C5,7,$D$13:$D$19)
  • In this argument, B5 denotes the starting date and C5 denotes the total days.
  • 7 is the weekend index of Friday-Saturday that you have to select from the drop-down while moving to the 3rd argument input.
  • In the 4th argument, the cell range of D13:D19 includes the holidays as the date format. We have used absolute cell references for D13:D19 here since we had to apply Fill Handle to autofill the other cells in Column D, otherwise, the reference cells would have been manipulated and the function would return with wrong outputs.
  • Next, press Enter.
  • Therefore, you will get the following workday.

apply WORKDAY.INTL function

  • Then, drag the Fill Handle icon to fill the other cells with the formulas.
  • As a result, you will receive the following upcoming workdays, including any weekends and holidays.

determine workday including weekends and holidays


2 Practical Examples of WORKDAY.INTL Function in Excel

We’ll now see two practical examples of how this WORKDAY.INTL function works in Excel.


1. Finding Project End Date by Using WORKDAY.INTL Function

For this example, let’s first get acquainted with our dataset. Column B contains a list of Product No. Column C contains the project start dates, and Column D contains the number of days required to process those projects. In Column E, we’ll find the end date of each project after taking into account all weekends and holidays.

The weekends in our dataset are Monday and Tuesday. To complete the task, you must follow the steps below.

  • First of all, type the following formula in cell D5. (Keep in mind that when using this formula, the serial numbers must be formatted as dates)
=WORKDAY.INTL(C5,D5,"1100000",$E$13:$E$18)
  • In this argument, C5 denotes the starting date and D5 denotes the total days.
  • 1100000 is the weekend index of Monday and Tuesday.
  • In the 4th argument, the cell range of E13:E18 includes the holidays as the date format. We have used absolute cell references for E13:E18 here since we had to apply Fill Handle to autofill the other cells in Column D, otherwise, the reference cells would have been manipulated and the function would return with wrong outputs.
  • Next, press Enter.
  • Therefore, you will get the following end date of the first project.

use of WORKDAY.INTL function

  • Then, drag the Fill Handle icon to fill the other cells with the formulas.
  • As a result, you will receive the following project completion date, including any weekends or holidays.

finding project end date

Weekend string: an array of 7 0s and 1s representing the seven consecutive days of the week, starting with Monday. A non-working day is represented by 1 and a working day is represented by 0. As an example,

  • “0000000”- No weekends
  • “1000000”- Monday is the weekend.
  • “0000011”- Saturday and Sunday are weekends.
  • “1100000”- Monday and Tuesday are weekends.
  • “1110000”- Monday, Tuesday, and Wednesday are weekends.
  • “1010000”- Monday and Wednesday are weekends.
  • “1000001”- Monday and Sunday are weekends.

2. Determining Next Shipment Date by Using WORKDAY.INTL Function

Let’s get introduced to our dataset first for this 2nd example. In Column B, there lies a number of Product IDs. Column C contains the order dates of those products and Column D tells us the number of days needed to process those orders. In Column E, we’ll find out the shipment or delivery dates based on those processing days after excluding all weekends and holidays.

In our dataset, the weekends are Friday and Saturday. And the holidays are listed under the Holidays header in Column E. To complete the task, you must follow the steps below.

  • First of all, type the following formula in cell D5. (Keep in mind that when using this formula, the serial numbers must be formatted as dates)
=WORKDAY.INTL(C5,D5,7,$E$13:$E$19)

In the argument section,

C5 is the order date for the 1st product ID.

D5 denotes the number of days needed to process.

7 is the weekend index of Friday-Saturday that you have to select from the drop-down while moving to the 3rd argument input.

➤ In the 4th argument, the cell range of E13:E21 includes the holidays as the date format. I’ve used absolute cell references for E13:E21 here since I had to apply Fill Handle to autofill the other cells in Column E, otherwise, the reference cells would have been manipulated and the function would return with wrong outputs.

  • Next, press Enter.
  • Therefore, you will get the following first shipment date.

apply WORKDAY.INTL Function

  • Then, drag the Fill Handle icon to fill the other cells with the formulas.
  • As a result, you will receive the following shipment date for each product, including any weekends or holidays.

finding next shipment date

Note: If you don’t want to see and convert date codes, then you have to use the TEXT function before the WORKDAY function to show dates with a specified format. So, in that case, the related formula in Cell E5 will be:

=TEXT(WORKDAY.INTL(C5,D5,7,$E$13:$E$21),"mm/dd/yyyy")

And the formula will return with 11/1/2021 as in the picture below.

formatting the shipment date


Creating a List of Dates (Dynamic Arrays) in Excel

Here, we will demonstrate how to create a list of dates (dynamic arrays) in Excel. To do the task, you have to follow the following procedure.

  • First of all, type the following formula in cell D5. (Keep in mind that when using this formula, the serial numbers must be formatted as dates)
=WORKDAY.INTL("01/01/2021",SEQUENCE(9),"1000011",Table2)

In this argument,

  • SEQUENCE(9): This array function generates a series of nine numbers beginning with 1.
  • 1100000 is the weekend index of Saturday and Sunday.
  • Table2 is a named range or table that contains any holidays that should be excluded from the calculation.
  • Next, press Enter.
  • Therefore, you will get the following output.

creating a list of dates


WORKDAY vs. WORKDAYS.INTL Function in Excel

The WORKDAY and WORKDAY.INTL functions are similar in that they both calculate a future or past date that excludes weekends and holidays, but they have some differences in how they function and are used. There are a few distinctions:

  • The syntax for the two functions differs somewhat. The WORKDAY function has the following syntax: WORKDAY(start_date, days, [holidays]). The WORKDAY.INTL function has the following syntax: WORKDAY.INTL(start_date, days, [weekend], [holidays]). An additional optional argument to the WORKDAY.INTL function specifies which days of the week are considered weekends.
  • Weekend days: By default, the WORKDAY function assumes weekends are Saturday and Sunday and omits them from the calculation. The WORKDAY.INTL function allows you to select which days of the week are designated weekends.

Frequently Asked Questions

1. When to use Excel WORKDAY.INTL Function.

The Excel WORKDAY.INTL function is useful when you need to calculate a future or past date that excludes weekends and holidays, but the weekend days are not Saturday and Sunday. It allows you to specify which days of the week are considered weekends and to exclude them from the calculation.

2. What does the weekend argument represent?

The weekend argument, which is optional, defines which days of the week are regarded as weekends. Seven digits are used to represent it, with “1” standing for a weekend day and “0” for a workday. The weekend days, Saturday and Sunday, are represented by the default value of “0000000”.

3. What does the holidays argument represent?

An optional argument that specifies a range or array of dates to be excluded from the calculation is the holidays argument. It can be a range of cells or a named range.

4. Can I use the WORKDAY.INTL function to calculate workdays for a specific region?

Yes, you can use WORKDAY.INTL function to determine the number of workdays in any region that doesn’t have Saturday and Sunday as the only weekend days. Just modify your weekend argument accordingly.


💡 Things to Keep in Mind

  • The start_date argument must contain dates with proper date format otherwise, the function will return #NUM error.
  • If you don’t use the weekend index as 3rd argument, the default weekends will be considered as Saturday-Sunday.
  •  Excel stores dates as sequential orders starting with 1 from the date of 1/1/1900. So if your start_date contains a date before 1/1/1900, then the function will return with a #NUM error.
  • For the [holidays] argument, you have to use the absolute cell references if you opt to fill down the cells under the 1st output.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try yourself while you go through the step-by-step process.


Conclusion

I hope this article on the use of WORKDAY.INTL will now prompt you to apply the function in Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

Keep learning new methods and keep growing!


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo