## Key

• This line was removed.
• Formatting was changed.

This example shows you the functions that can be used to calculate the number of days between two input dates:

• `DATEDIF` - Calculates difference between two input dates for a specified unit of measure. In this example, the unit of measure is day. See DATEDIF Function.
• `NETWORKDAYS` - Calculates number of working days between two input dates, assuming a Monday - Friday workweek. See NETWORKDAYS Function.
• `NETWORKDAYSINTL` - Calculates number of working days between two input dates with optional specified workweek. see NETWORKDAYSINTL Function.
• `WORKDAY` - Calculates the date of a working day that is a specified number of working days before or after a specified date. See WORKDAY Function.
• `WORKDAYINTL` - Calculates the date of a working day that is a specified number of working days before or after a specified date, factoring in an optional set of workday schedule for the week. See 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
RawWrangle true 'datedif' step derive type: single value: datedif(Date1, Date2, day) as: 'datedif' Formula type Single row formula Formula datedif(Date1, Date2, day) New column name 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 'networkDays' step derive type: single value: networkdays(Date1, Date2, []) as: 'networkDays' Formula type Single row formula Formula networkdays(Date1, Date2, []) New column name 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 'networkDaysStPatricks' step derive type: single value: networkdays(Date1, Date2, ['2020-03-17']) as: 'networkDaysStPatricks' Formula type Single row formula Formula networkdays(Date1, Date2, ['2020-03-17']) New column name 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 'networkDaysIntl' step derive type: single value: networkdaysintl(Date1, Date2, '1000011', []) as: 'networkDaysIntl' Formula type Single row formula Formula networkdaysintl(Date1, Date2, '1000011', []) New column name 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 'workday' step derive type: single value: workday(Date2, -5) as: 'workday' Formula type Single row formula Formula workday(Date2, -5) New column name 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 'workdayintl' step derive type: single value: workdayintl(Date2, -5,'0000111') as: 'workdayintl' Formula type Single row formula Formula workdayintl(Date2, -5,'0000111') New column name New 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