| Excerpt |
|---|
Calculates the number of working days between two specified dates. Optionally, you can specify which days of the week are working days as an input parameter. Optional list of holidays can be specified. |
- Inputs can be column references or the outputs of the DATE or TIME functions.
- The first value is used as the baseline to compare the date values.
- If the first date value occurs after the second date value, a null value is returned.
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:datedif(StartDate, EndDate, month) |
|---|
|
networkdaysintl(StartDate, EndDate) |
Output: Returns the number of working days between StartDate and EndDate.
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | syntax |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:datedif(date1,date2,date_units) |
|---|
|
networkdaysintl(date1,date2[,str_workingdays][,array_holiday]) |
| Argument | Required? | Data Type | Description |
|---|
| date1 | Y | datetime | Starting date to compare |
| date2 | Y | datetime | Ending date to compare |
| str_workingdays | N | string | Seven-character string identifying the weekend days. |
| array_holiday | N | array | An array of string values representing the valid dates of holidays. |
date1, date2
Date values can be column references or output of the DATE function or the TIME function.
Date values to compared in working days.
- If
date2 > date1, then results are positive.
- If
date2 < date1, then a null value is returned.
If date1 and date2 have a specified time zone offset, the function calculates the difference including the timezone offsets.
- If
date1 does not have a specified time zone but date2 does, the function uses the local time in the same time zone as date2 to calculate the difference. The functions returns the difference without the time zone offset.
| Required? | Data Type | Example Value |
|---|
| Yes | Datetime (Column reference or date output of DATE or TIME function) | LastContactDate |
str_workingdays
A seven-character string identifying the days of the week that are working days.
- String value must be seven characters long and contain only
0 or 1 characters. All other values are ignored. - First character in the string represents Monday and last character in the string represents Sunday.
- If the string is not specified, then a Monday - Friday workweek is used.
Examples:
| str_workingdays | Weekend days |
|---|
'0000011' | Saturday and Sunday (default) |
'1000011' | Monday, Saturday, and Sunday |
'0000000' | None. |
| Required? | Data Type | Example Value |
|---|
| Yes | Array | ['1000011'] |
array_holiday
An array containing the list of holidays, which are factored in the calculation of working days.
Values in the array must be in either of the following formats:
| Code Block |
|---|
['2020-12-24','2020-12-25']
['2020/12/24','2020/12/25'] |
| Required? | Data Type | Example Value |
|---|
| Yes | Array | ['2018-12-24','2018-12-25','2018-12-31','2019-01-01'] |
Example - Date diffing functions
| Include Page |
|---|
| EXAMPLE - Date Difference Functions |
|---|
| EXAMPLE - Date Difference Functions |
|---|
|
