EXAMPLE - Date Difference Functions

Contents:

Expand all Collapse all

NOTE: Designer Cloud Educational is a free product with limitations on its features. Some features in the documentation do not apply to this product edition. See Product Limitations.

This example demonstrates how to calculate the number of days between two input dates.

Functions:

Item	Description
$strPageTitle	Error rendering macro 'excerpt-include' No link could be created for '$strPageTitle'.
$strPageTitle	Error rendering macro 'excerpt-include' No link could be created for '$strPageTitle'.
$strPageTitle	Error rendering macro 'excerpt-include' No link could be created for '$strPageTitle'.
$strPageTitle	Error rendering macro 'excerpt-include' No link could be created for '$strPageTitle'.
$strPageTitle	Error rendering macro 'excerpt-include' No link could be created for '$strPageTitle'.

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	`New formula`
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	`New formula`
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	`New formula`
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	`New formula`
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	`New formula`
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	`New formula`
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

See Also for EXAMPLE - Date Difference Functions:

Error rendering macro 'contentbylabel'

parameters should not be empty

Send Feedback

Page tree

EXAMPLE - Date Difference Functions