| Excerpt |
|---|
This example demonstrates how to calculate the number of days between two input dates. |
Functions:
| D generate list excerpts |
|---|
| pages | DATEDIF Function,NETWORKDAYS Function,NETWORKDAYSINTL Function,WORKDAY Function,WORKDAYINTl Function |
|---|
|
Source:
The following dataset contains two columns of dates.
- The first column values are constant. This date falls on a Monday.
| Date1 | Date2 |
|---|
| 2020-03-09 | 2020-03-13 |
| 2020-03-09 | 2020-03-06 |
| 2020-03-09 | 2020-03-16 |
| 2020-03-09 | 2020-03-23 |
| 2020-03-09 | 2020-04-10 |
| 2020-03-09 | 2021-03-10 |
Transformation:
The first transformation calculates the number of raw days between the two dates:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'datedif' |
|---|
| Type | step |
|---|
| WrangleText | derive type: single value: datedif(Date1, Date2, day) as: 'datedif' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | datedif(Date1, Date2, day) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
This step computes the number of working days between the two dates. Assumptions:
- Workweek is Monday - Friday.
- There are no holidays.
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'networkDays' |
|---|
| Type | step |
|---|
| WrangleText | derive type: single value: networkdays(Date1, Date2, []) as: 'networkDays' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | networkdays(Date1, Date2, []) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
For some, March 17 is an important date, especially if you are Irish. To add St. Patrick's Day to the list of holidays, you could add the following transformation:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'networkDaysStPatricks' |
|---|
| Type | step |
|---|
| WrangleText | derive type: single value: networkdays(Date1, Date2, ['2020-03-17']) as: 'networkDaysStPatricks' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | networkdays(Date1, Date2, ['2020-03-17']) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
In the following transformation, the NETWORKDAYSINTL function is applied so that you can specify the working days in the week:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'networkDaysIntl' |
|---|
| Type | step |
|---|
| WrangleText | derive type: single value: networkdaysintl(Date1, Date2, '1000011', []) as: 'networkDaysIntl' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | networkdaysintl(Date1, Date2, '1000011', []) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
The following two functions enable you to calculate a specific working date based on an input date and integer number of days before or after it. In the following, the date that is five working days before the Date2 column is computed:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'workday' |
|---|
| Type | step |
|---|
| WrangleText | derive type: single value: workday(Date2, -5) as: 'workday' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | workday(Date2, -5) |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
Suppose you wish to factor in a four-day workweek, in which Friday through Sunday is considered the weekend:
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | 'workdayintl' |
|---|
| Type | step |
|---|
| WrangleText | derive type: single value: workdayintl(Date2, -5,'0000111') as: 'workdayintl' |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | workdayintl(Date2, -5,'0000111') |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
Results:
| Date1 | Date2 | workdayintl | workday | networkDaysIntl | networkDaysStPatricks | networkDays | datedif |
|---|
| 2020-03-09 | 2020-03-13 | 2020-03-05 | 2020-03-06 | 4 | 5 | 5 | 4 |
| 2020-03-09 | 2020-03-06 | 2020-02-27 | 2020-02-28 | null | null | null | -3 |
| 2020-03-09 | 2020-03-16 | 2020-03-15 | 2020-03-09 | 4 | 6 | 6 | 7 |
| 2020-03-09 | 2020-03-23 | 2020-03-12 | 2020-03-16 | 8 | 10 | 11 | 14 |
| 2020-03-09 | 2020-04-10 | 2020-04-02 | 2020-04-03 | 20 | 24 | 25 | 32 |
| 2020-03-09 | 2021-03-10 | 2021-03-02 | 2021-03-03 | 210 | 262 | 263 | 366 |
| D s also |
|---|
| label | example_date_difference_functions |
|---|
|
