How to Use WORKDAY.INTL Function in Excel (A Complete Guideline)

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 WORKDAY.INTL function efficiently in Excel.

workday.intl function in excel overview

The above screenshot is an overview of the article which represents an application of the WORKDAY.INTL function in Excel. 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.


Download Practice Workbook

You can download the Excel workbook that we have used to prepare 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.


Determining Next Working Day by Using WORKDAY.INTL

We’ll now see with an appropriate example of how this WORKDAY.INTL function works in Excel.

Step 1: Introduction to the Dataset

Let’s get introduced to our dataset first. In Column B, there lies a number of Product IDs. Column C occupies 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.

workday.intl function in excel to find next working shipment date


Step 2: Inserting WORKDAY.INTL Function

In the output Cell E5, we have to type:

=WORKDAY.INTL(C5,D5,7,$E$13:$E$21)

After pressing Enter and auto-filling the rest of the cells in Column E with Fill Handle, we’ll see a number of date codes that have to be formatted to dates later.

workday.intl function to find next working shipment date

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.


Similar Readings


Step 3: Converting Resultant Date Code into Date Format

Now select the cell range of E5:E10. Under the Home tab, select any of the date formats between Short Date and Long Date from the Number group of commands. I’ve selected Long Date here to show dates more precisely.

workday.intl function to find next working shipment date

And finally, the date codes will convert into date format. In the following screenshot, you’re seeing the shipment dates in Column E with the defined weekends and holidays criteria.

workday.intl function to find next working shipment date

Note: If you don’t want to see and convert date codes, then you have to use the TEXT function before 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.

workday.intl function to find next working shipment date


💡 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 date 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 #NUM error.

🔺 For [holidays] argument, you have to use the absolute cell references if you opt to fill down the cells under the 1st output.


Concluding Words

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.


Related Articles

Nehad Ulfat

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo