Home »
Excel-Built-In-Functions »
Excel-Date-And-Time-Functions »
Excel-Workday.Intl-Function
The Excel WORKDAY.INTL Function
Function Description
The Excel Workday.Intl function returns a date that is a supplied number of working days
(excluding weekends and holidays) ahead of a given start date. The function allows the user
to specify which days of the week are counted as weekends.
The function is new in Excel 2010 and so is not available in earlier versions of Excel.
However, it is similar to the
Workday function,
which is available in earlier versions of Excel.
The syntax of the Workday.Intl function is:
WORKDAY.INTL( start_date, days,
[weekend], [holidays] )
where the arguments are as follows:
| start_date |
- |
The initial date, from which to count the number of workdays.
|
| days |
- |
The number of workdays to add onto start_date.
|
| [weekend] |
- |
An optional argument, which specifies which weekdays should be counted as the weekend.
This can be either a number or a string, as explained below:
|
|
Possible number values for the [weekend] argument are:
| [weekend] |
days counted
as weekend |
1
(or omitted) |
Sat & Sun |
| 2 |
Sun & Mon |
| 3 |
Mon & Tue |
| 4 |
Tue & Wed |
| 5 |
Wed & Thu |
| 6 |
Thu & Fri |
| 7 |
Fri & Sat |
| 11 |
Sunday only |
| 12 |
Monday only |
| 13 |
Tuesday only |
| 14 |
Wednesday only |
| 15 |
Thursday only |
| 16 |
Friday only |
| 17 |
Saturday only |
|
Possible string values for the [weekend] argument consist of
a series of seven 0's and 1's which represent the seven weekdays, starting from Monday.
Each 1 denotes a day that should be counted as a weekend and each 0 represents a working day.
For example,
| 0000100 |
- |
denotes Fridays only counted as weekend days |
| 0001100 |
- |
denotes Thursdays and Fridays counted as weekend days |
| 0000111 |
- |
denotes Fridays, Saturdays and Sundays counted as weekend days |
The string "1111111" is not valid.
|
|
| [holidays] |
- |
An optional argument, which specifies an array of dates (in addition to weekends) that are not
to be counted as working days.
|
Note that Microsoft recommends that the start_date and
[holidays] arguments should be input as either:
- References to cells containing dates
or
- Dates returned from formulas.
If you attempt to input Excel date arguments as text, Excel may misinterpret them, depending on the
date system, or date interpretation settings, on your computer.
Workday.Intl Examples
The spreadsheets below show three examples of the Excel Workday.Intl function. In all three examples,
the function is used to calculate the date that is 25 work days after Dec 01, 2015. However, the
weekends and holidays are different in each case.
|
Formulas:
| |
A |
B |
| 1 |
Start Date: |
12/01/2015 |
| 2 |
Holidays: |
12/25/2015 |
| 3 |
|
12/28/2015 |
| 4 |
|
01/01/2016 |
| 5 |
|
|
| 6 |
Date Falling 25 work days after Dec 1st, 2015: |
| 7 |
=WORKDAY.INTL( B1, 25 ) |
| 8 |
=WORKDAY.INTL( B1, 25, 1, B2:B4 ) |
| 9 |
=WORKDAY.INTL( B1, 25, "0000111" ) |
|
|
Results:
| |
A |
B |
| 6 |
Date Falling 25 work days after Dec 1st, 2015: |
| 7 |
01/05/2016 |
|
| 8 |
01/08/2016 |
|
| 9 |
01/13/2016 |
|
|
Note that, in the above formulas:
- In the example in cell A7, the weekends have the default setting (falling on Saturdays and Sundays),
and so the [weekend] argument can be omitted from the function;
- In the examples in cells A7 and A9, no additional holidays are to be included in the calculations,
and so the [holidays] argument is omitted from the functions;
- In the example in cell A9, the text string "0000111" defines the weekends to be Fridays, Saturdays and Sundays.
For further examples of the Excel Workday.Intl function, see the
Microsoft Office website.
Workday.Intl Errors
If you get an error from the Excel Workday.Intl function this is likely to be one of the following:
Common Errors
| #NUM! |
- |
Occurs if either:
- The supplied start_date plus the supplied days
argument results in an invalid date;
- The supplied [weekend] argument is an invalid numeric value.
|
| #VALUE! |
- |
Occurs if either:
- The supplied start_date or any of the values in the supplied
[holidays] array are not valid dates;
- the supplied [weekend] argument is an invalid text string;
- The supplied days argument is non-numeric.
|