WORKDAY( ) function

Returns the number of workdays between two dates.

Syntax

WORKDAY(start_date, end_date <,nonworkdays>)

Parameters

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 literal. Specifies which days of the week are weekend days, or non workdays, and excludes them from the calculation. If you omit the nonworkdays parameter, Saturday and Sunday are used as the default non workdays.

Enter nonworkdays using the following abbreviations, separated by a space or a comma: Mon, Tue, Wed, Thu, Fri, Sat, Sun. The abbreviations must be entered in English even if you are using a localized version of ACL Analytics. The abbreviations are not case-sensitive. The nonworkdays parameter must be enclosed in quotation marks.

Output

Numeric. The number of workdays in the period for which workdays are calculated.

Remarks

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.

If the value in the start_date parameter is more recent than the value in the end_date parameter, a negative value is returned. Both parameters can accept a datetime value, but the time portion of the value is ignored.

A field specified for the start_date and end_date parameters can use any date format, as long as the field definition correctly defines the format.

When specifying a literal date value for the start_date or end_date parameters, you are restricted to the formats YYYYMMDD and YYMMDD, and you must enclose the value in backquotes – for example, `20141231`.

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.

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 scripts from ScriptHub” in the ACL Analytics User Guide.

For shorter time periods with only three or four holidays, such as a quarter, you may find creating the conditional computer 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 period:

The example that follows accommodates periods that have any start date and end date during the quarter.

You would first create a computed field, for example “Workdays”, that calculates the workdays for all periods during the quarter:

DEFINE FIELD Workdays COMPUTED WORKDAY(Start_date, End_date)

You would then create a conditional computed field, for example “Workdays_no_holidays”, that makes use of 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 example is important. ACL 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.

Example(s)

Example

Return value

WORKDAY(`20150302`, `20150308`)

5

The number of workdays between Monday, March 02, 2015 and Sunday, March 08, 2015 (inclusive).

WORKDAY(`20150302`, `20150308`, "Sun")

6

The number of workdays between Monday, March 02, 2015 and Sunday, March 08, 2015 (inclusive), when Sunday is the only non workday.

WORKDAY(`20150301`, `20150307`, "Fri Sat")

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(Start_date, `20151231`)

The number of workdays between each date in the Start_date field and December 31, 2015 (inclusive).

WORKDAY(Start_date, End_date)

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.



(C) 2015 ACL Services Ltd. All Rights Reserved.