WORKDAY( ) function
Returns the number of workdays between two dates.
Syntax
WORKDAY(start_date, end_date <,nonworkdays>)
Parameters
| Name | Type | Description |
|---|---|---|
| start_date |
datetime |
The start date of the period for which workdays are calculated. The start date is included in the period. |
| end_date | datetime | The end date of the period for which workdays are calculated. The end date is included in the period. |
| nonworkdays
optional |
character |
The days of the week that are weekend days, or non workdays, and excluded from the calculation. If nonworkdays is omitted, Saturday and Sunday are used as the default non workdays. Enter nonworkdays using the following abbreviations, separated by a space or a comma:
nonworkdays is not case-sensitive. The abbreviations must be entered in English even if you are using a non-English version of Analytics: "Fri, Sat, Sun" |
Note
You can specify a datetime value for start_date or end_date but the time portion of the value is ignored.
If start_date is more recent than end_date, a negative value is returned.
Output
Numeric. The number of workdays in the period for which workdays are calculated.
Examples
Basic examples
Literal input values
Returns 5 (the number of workdays between Monday, March 02, 2015 and Sunday, March 08, 2015 inclusive):
WORKDAY(`20150302`, `20150308`)
Returns 6 (the number of workdays between Monday, March 02, 2015 and Sunday, March 08, 2015 inclusive, when Sunday is the only non workday):
WORKDAY(`20150302`, `20150308`, "Sun")
Returns 5 (the number of workdays between Sunday, March 01, 2015 and Saturday, March 07, 2015 inclusive, when Friday and Saturday are the non workdays):
WORKDAY(`20150301`, `20150307`, "Fri, Sat")
You can also use the function to calculate the number of weekend days in a date range.
Returns 2 (the number of weekend days between Monday, March 02, 2015 and Sunday, March 08, 2015 inclusive):
WORKDAY(`20150302`, `20150308`, "Mon, Tue, Wed, Thu, Fri")
Field input values
Returns the number of workdays between each date in the Start_date field and December 31, 2015 inclusive:
WORKDAY(Start_date, `20151231`)
Returns the number of workdays between each date in the Start_date field and a corresponding date in the End_date field inclusive:
- Statutory holidays are included in the workdays total and may need to be factored out using a separate calculation
- A negative return value indicates a start date that is more recent than an end date
WORKDAY(Start_date, End_date)
Remarks
Date formats
A field specified for start_date or end_date can use any date format, as long as the field definition correctly defines the format.
When specifying a literal date value for start_date or end_date, you are restricted to the formats YYYYMMDD and YYMMDD, and you must enclose the value in backquotes – for example, `20141231`.
Non workdays other than Saturday and Sunday
The ability to specify non workdays other than Saturday and Sunday allows you to use the WORKDAY( ) function with data that is not based on a Monday-to-Friday work week, or on a five-day work week.
For example, if you specify "Sun" by itself as a non workday, you create a six-day work week from Monday to Saturday.
Accounting for statutory holidays
The WORKDAY( ) function does not take into account statutory holidays, which means that the return value may not reflect the actual number of workdays in a period if the period contains one or more statutory holidays.
"Calculate Working Days" script in ScriptHub
If you need to account for statutory holidays, one option is to use the Calculate Working Days script in ScriptHub, which accepts a list of user-defined holidays.
For data that covers longer time periods and includes a number of holidays, using the script is probably the easier approach. For more information, see Importing from ScriptHub.
For shorter time periods with only three or four holidays, such as a quarter, you may find creating the conditional computed field described below is not too laborious.
Conditional computed field for deducting statutory holidays
If required, you can create a conditional computed field to deduct statutory holidays from the value returned by the WORKDAY( ) function.
For example, for first-quarter data for 2015, you could decrement the WORKDAY( ) return value by 1 for each of these holidays that falls into a specified period:
- 01 January 2015
- 19 January 2015
- 16 February 2015
The example that follows accommodates periods that have any start date and end date during the quarter.
You first create a computed field, for example Workdays, that calculates the workdays for a specified period during the quarter:
DEFINE FIELD Workdays COMPUTED WORKDAY(Start_date, End_date)
You then create a conditional computed field, for example Workdays_no_holidays, that adjusts the value returned by the first computed field (Workdays):
DEFINE FIELD Workdays_no_holidays COMPUTED Workdays-1 IF Start_date = `20150101` AND End_date < `20150119` Workdays-2 IF Start_date = `20150101` AND End_date < `20150216` Workdays-3 IF Start_date = `20150101` AND End_date <= `20150331` Workdays IF Start_date < `20150119` AND End_date < `20150119` Workdays-1 IF Start_date < `20150119` AND End_date < `20150216` Workdays-2 IF Start_date < `20150119` AND End_date <= `20150331` Workdays-1 IF Start_date = `20150119` AND End_date < `20150216` Workdays-2 IF Start_date = `20150119` AND End_date <= `20150331` Workdays IF Start_date < `20150216` AND End_date < `20150216` Workdays-1 IF Start_date < `20150216` AND End_date <= `20150331` Workdays-1 IF Start_date = `20150216` AND End_date <= `20150331` Workdays IF Start_date < `20150331` AND End_date <= `20150331` Workdays
Note
The order of the conditions in the conditional computed field is important.
Analytics evaluates multiple conditions starting at the top. The first condition that evaluates to true for a record assigns the value of the conditional computed field for that record. A subsequent condition that evaluates to true does not change the assigned value.
