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.
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
The WORKDAY.INTL function returns the serial number of the date before or after a specified number of weekdays with custom weekend parameters.
=WORKDAY.INTL(start_date, days, [weekend], [holidays])
|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.|
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.
Step 2: Inserting WORKDAY.INTL Function
In the output Cell E5, we have to type:
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.
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.
- Excel Current Time Formula (7 Suitable Examples)
- How to Use WEEKNUM Function in Excel (2 Ways)
- Remove Time from Date in Excel (6 Approaches)
- How to Auto-Update Current Time in Excel (With Formula and VBA)
- How to Use the Excel DAYS Function with a Practical Example
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.
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.
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:
And the formula will return with 11/1/2021 as in the picture below.
💡 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.
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.