Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r091
Excerpt

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

Functions:

D generate list excerpts
pagesDATEDIF 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.
Date1Date2
2020-03-092020-03-13
2020-03-092020-03-06
2020-03-092020-03-16
2020-03-092020-03-23
2020-03-092020-04-10
2020-03-092021-03-10

Transformation:

The first transformation calculates the number of raw days between the two dates: 

D trans
RawWrangletrue
p03Value'datedif'
Typestep
WrangleTextderive type: single value: datedif(Date1, Date2, day) as: 'datedif'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuedatedif(Date1, Date2, day)
p03NameNew column name
SearchTermNew formula

This step computes the number of working days between the two dates. Assumptions:

  • Workweek is Monday - Friday.
  • There are no holidays.

D trans
RawWrangletrue
p03Value'networkDays'
Typestep
WrangleTextderive type: single value: networkdays(Date1, Date2, []) as: 'networkDays'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuenetworkdays(Date1, Date2, [])
p03NameNew column name
SearchTermNew 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
RawWrangletrue
p03Value'networkDaysStPatricks'
Typestep
WrangleTextderive type: single value: networkdays(Date1, Date2, ['2020-03-17']) as: 'networkDaysStPatricks'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuenetworkdays(Date1, Date2, ['2020-03-17'])
p03NameNew column name
SearchTermNew formula

In the following transformation, the NETWORKDAYSINTL function is applied so that you can specify the working days in the week:

D trans
RawWrangletrue
p03Value'networkDaysIntl'
Typestep
WrangleTextderive type: single value: networkdaysintl(Date1, Date2, '1000011', []) as: 'networkDaysIntl'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valuenetworkdaysintl(Date1, Date2, '1000011', [])
p03NameNew column name
SearchTermNew 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
RawWrangletrue
p03Value'workday'
Typestep
WrangleTextderive type: single value: workday(Date2, -5) as: 'workday'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valueworkday(Date2, -5)
p03NameNew column name
SearchTermNew formula

Suppose you wish to factor in a four-day workweek, in which Friday through Sunday is considered the weekend:

D trans
RawWrangletrue
p03Value'workdayintl'
Typestep
WrangleTextderive type: single value: workdayintl(Date2, -5,'0000111') as: 'workdayintl'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02Valueworkdayintl(Date2, -5,'0000111')
p03NameNew column name
SearchTermNew formula

Results:

Date1Date2workdayintlworkdaynetworkDaysIntlnetworkDaysStPatricksnetworkDaysdatedif
2020-03-092020-03-132020-03-052020-03-064554
2020-03-092020-03-062020-02-272020-02-28nullnullnull-3
2020-03-092020-03-162020-03-152020-03-094667
2020-03-092020-03-232020-03-122020-03-168101114
2020-03-092020-04-102020-04-022020-04-0320242532
2020-03-092021-03-102021-03-022021-03-03210262263366

D s also
labelexample_date_difference_functions