EXAMPLE - Date Difference Functions
This example demonstrates how to calculate the number of days between two input dates.
Functions:
Item | Description |
|---|---|
DATEDIF Function | Calculates the difference between two valid date values for the specified units of measure. |
NETWORKDAYS Function | Calculates the number of working days between two specified dates, assuming Monday - Friday workweek. Optional list of holidays can be specified. |
NETWORKDAYSINTL Function | 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. |
WORKDAY Function | Calculates the work date that is before or after a start date, as specified by a number of days. A set of holiday dates can be optionally specified. |
WORKDAYINTL Function | Calculates the work date that is before or after a start date, as specified by a number of days. You can also specify which days of the week are working days and a list of holidays via parameters. |
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:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | datedif(Date1, Date2, day) |
Parameter: New column name | 'datedif' |
This step computes the number of working days between the two dates. Assumptions:
Workweek is Monday - Friday.
There are no holidays.
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | networkdays(Date1, Date2, []) |
Parameter: New column name | 'networkDays' |
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:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | networkdays(Date1, Date2, ['2020-03-17']) |
Parameter: New column name | 'networkDaysStPatricks' |
In the following transformation, the NETWORKDAYSINTL function is applied so that you can specify the working days in the week:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | networkdaysintl(Date1, Date2, '1000011', []) |
Parameter: New column name | 'networkDaysIntl' |
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:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | workday(Date2, -5) |
Parameter: New column name | 'workday' |
Suppose you wish to factor in a four-day workweek, in which Friday through Sunday is considered the weekend:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | workdayintl(Date2, -5,'0000111') |
Parameter: New column name | 'workdayintl' |
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 |