|
The Excel WORKDAY.INTL Function
Basic 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 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 format 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 weekends.
This can be either a number or a string. These are 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 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 these date arguments as text, Excel may misinterpret
them, due to different date systems, or date interpretation settings.
Warning:
Although you can input date arguments as date serial numbers, this is not recommended
as date serial numbering does vary across different computer systems.
Examples
The spreadsheets below show examples of the Excel Workday.Intl function.
The format of the function is shown in the spreadsheet at the top and the results are shown below.
Formulas:
|
Results:
|
In the above spreadsheets :
- In the example in cell D2 the holidays array has been omitted.
Therefore the calculation excludes Saturdays and Sundays but includes all other weekdays,
including the holidays at Christmas and New Year.
- In the example in cell D3 the [weekend] argument is 1 (specifying weekends
on Saturdays and Sundays) and the holidays array (in cells B2 - B4) is provided to the Workday.Intl function.
Therefore the calculation excludes Saturdays and Sundays and the listed Christmas and New Year holidays.
- In the example in cell D4 Fridays, Saturdays and Sundays are specified as weekends, but no holiday array has
been supplied to the function. Therefore the calculation excludes Fridays, Saturdays and Sundays but includes
all other weekdays, including the holidays at Christmas and New Year.
Note also that, as recommended by Microsoft, in all three calls to the Workday.Intl function, the start_date and
[holidays] arguments have been supplied as cell references.
Further examples of the Excel Workday.Intl function can be found on the
Microsoft Office website.
Trouble Shooting
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 |
| or | |
| - |
the supplied [weekend] argument is invalid (see above explanation of this argument) |
|
|
#VALUE!
|
-
|
Occurs if either:
| - |
the supplied start_date or any of the values in the supplied [holidays] array are not valid dates |
| or | |
| - |
the supplied days argument is non-numeric |
|
|
