|
The Excel WORKDAY Function
Basic Description
The Excel Workday function returns a date that is a supplied number of working days (excluding weekends
and holidays) ahead of a given start date.
The format of the function is:
WORKDAY( start_date, days, [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
|
| [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 simple examples of the Excel Workday function.
The format of the function is shown in the top spreadsheet 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 examples in cells D3 and D4 the holidays array (in cells B2 - B4) is provided to the Workday function.
Therefore the calculation excludes Saturdays and Sundays and the listed Christmas and New Year holidays.
Note also that, as recommended by Microsoft, in all three calls to the Workday function, the start_date and
[holidays] arguments have been supplied as either cell references or the return value from a function (in
this case the Excel Date function).
Further examples of the Excel Workday function can be found on the
Microsoft Office website.
Trouble Shooting
If you get an error from the Excel Workday function this is likely to be one of the following :
Common Errors
|
#NUM!
|
-
|
Occurs if the supplied start_date plus the supplied days argument results in an invalid date
|
|
#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 |
|
|
#NAME?
|
-
|
Occurs when Analysis ToolPak add-in is not enabled in your Excel.
You will need to enable this if you want to use the Excel Workday function.
To do this in Excel 2003 :
- From the Tools drop-down menu, select the option Add-Ins ...
- An 'Add-Ins' window will pop up. From this, select the option Analysis ToolPak and click OK
To do this in Excel 2007 :
- Click the Microsoft button on the top left of your spreadsheet and select
the Excel Options button
- From the menu on the left hand side, select Add-Ins
- In the 'Manage:' box, select Excel Add-ins and click Go...
- An 'Add-Ins' window will pop up. From this, select the option Analysis ToolPak and click OK
To do this in Excel 2010 :
- Click the File tab (top left of your spreadsheet) and select Options
- From the menu on the left hand side, select Add-Ins
- If the window that pops up doesn't show the 'Add-ins' list, use the 'Manage:'
drop-down menu (at the bottom of the window) to select Excel Add-ins. Click Go...
- From the 'Add-Ins' window, select the option Analysis ToolPak and click OK
|
|
