What is Excel WORKDAY.INTL Function?
Excel WORKDAY.INTL function is a function under Excel Date and Time functions. It was introduced in Excel 2010, and is available in other subsequent versions.
Similar to the Excel WORKDAY function, WORKDAY.INTL function provides a future or past working date based on the number of working days, after excluding weekends and holidays specified.
It is however different from WORKDAY function because it allows us to specify any day, other than Saturday and Sunday as weekends.
How Excel WORKDAY.INTL Works?
With Excel WORKDAY.INTL function, it returns a future or past date after a specified number of working days (excluding weekends and holidays) has been added or deducted from the given start date.
This function takes any day of the week apart from Saturday or Sunday as weekends, as it allows the user to specify any day to be used as weekends.
The syntax for WORKDAY.INTL function is:
=WORKDAY.INTL(start_date, days, [weekend], [holidays])
And takes the following Input Arguments:
- Start_date – This is the initial date to start counting the workdays from.
- Days – This is the number of wok days to be added to the start_date.
- Weekend – This is optional. It indicates which days of the week to choose as weekends that will not be counted as working days. Weekend can be entered as a number value or string that specifies what days are weekends.
The number value for weekend days is:
The string values for [Weekend] argument takes a series of seven 0’s and 1’s that represents the seven days of the week. It starts from Monday and ends on Sunday.
Each 0 represents a work day, while each 1 represents a non-working day. See as explained in the additional notes below.
- [holidays] – This is also optional. It specifies an array of dates listed that are non-working days. The list can be specified as a range of cells containing the dates (A1:A7) or an array of serial numbers that represents the dates (44696, 44880, 44150)
When Excel WORKDAY.INTL Function is Used
The Excel WORKDAY.INTL function is used when making calculations that has to do with the number of workdays employees worked, or the expected delivery time of an order or a project, or to know the dates when invoices are due.
And because you can use any day as weekends, it can be used in different countries that have different days as weekends.
- A Date start_date and [holiday] arguments can be entered either as:
- reference to cells that contain the dates
- a date result from other function
- date stored as text
- the date entered as text (i.e in double quotes)
- If Excel dates are entered as text, it may be misinterpreted, since Excel interprets date depending on the date system on your computer.
- A weekend could be any two consecutive days or any single day of the week.
- Any single day or two consecutive days could be specified as weekend. If you do not specify any day for weekend, Excel will automatically take Saturday and Sunday as weekend. When you enter the weekend argument, Excel displays a drop-down menu for you to select the relevant option.
- For part-time jobs or jobs that have non-consecutive working days, you will enter the following format in the [Weekend] section of the formula:
- If you work only Mondays, Wednesday and Fridays, type in “0101011” (Working days are represented by 0, and non-working days represented by 1).
- In this format, the first number represents Monday and the last number represents Sunday.
- When using these format, the numbers should be in double quotes.
- Using the same logic, “1011011” indicates only 2 working days as Tuesday and Friday, and the other 5 days are non-working days.
- Using the string “1111111” is not valid.
How to Use Excel WORKDAY.INTL Function: Formulas & Examples
To understand how to use Excel WORKDAY.INTL function, let us take a look at some examples.
In the worksheet above, we have used WORKDAY.INTL function in three ways.
Suppose you want to get a future or past working date after adding or deducting a given number of days to a particular start_date, you may not specify the weekend if your weekend is Saturday and Sunday as it is shown in column C below.
You can use the formula:
Where A7 is the start_date, and B7 is the days argument.
This is because, by default, WORKDAY.INTL function counts only workdays. It does not count weekends which is automatically set to Saturdays and Sundays.
So even if you do not specify any weekend argument, Saturdays and Sundays are taken as weekends and excluded from the workdays.
Also, note that using this formula, the holidays are counted in the workdays because it was not specified in the holiday argument.
If you have a situation whereby your weekends are only Sundays or any other day of the week, in this case you use the formula below and as shown in column D of the worksheet.
Here, the start_date, days, weekends and holiday arguments are all specified. WORKDAY.INTL excludes only Sundays which is the number code 11 specified for weekends, as well as the cell range of G7:G17 containing the holiday dates.
The weekend argument can take any number code that corresponds with your weekend days displayed by Excel and as shown in the table under the Syntax section.
In a situation whereby you run part-time shifts for your business and employees work only on specific days of the week, Excel WORKDAY.INTL allows you to specify the weekend argument using string values that consists of a series of seven 0’s and 1’s that represent each day of the week starting from Monday. (See additional notes section above).
The formula used here is:
The format here indicates that WORKDAY.INTL will count Mondays, Tuesdays, Wednesdays and Thursdays as workdays, while Friday, Saturday and Sundays as weekends and therefore excluded from the workdays.
The 0’s signify workdays, while 1’s signify weekends.
Note that when using the format, the numbers must be in double quotes.
Common Errors in Excel WORKDAY.INTL Function
The following errors may occur in Excel WORKDAY.INTL function due to the following reasons:
- #NUM! – This error occurs if the start_date and days arguments specified are invalid dates; or the specified [Weekend] argument is an invalid numeric value.
- #VALUE! – This occurs if the specified start_date or the array of values specified in [holidays] are invalid dates; or the text string specified for [weekend] argument is invalid; or the value specified for days argument is non-numeric.
Other Related Excel Functions
Thank you for reading Excel WORKDAY.INTL function. You can master Excel by learning other Excel functions.
To learn more, check out these other functions in Excel:
- EDATE Function