Page tree



Contents:

The cloud-based version of Trifacta Wrangler is available! Read all about it, and register for your free account.

 

Contents:


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.
  • Inputs can be column references or the outputs of the DATE or TIME functions.
  • The first value is used as the baseline to compare the date values.
  • If the first date value occurs after the second date value, a null value is returned.

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

networkdaysintl(StartDate, EndDate)

Output: Returns the number of working days between StartDate and EndDate.

Syntax and Arguments

networkdaysintl(date1,date2[,str_workingdays][,array_holiday])


ArgumentRequired?Data TypeDescription
date1YdatetimeStarting date to compare
date2YdatetimeEnding date to compare
str_workingdaysNstring

Seven-character string identifying the weekend days.

array_holidayNarrayAn array of string values representing the valid dates of holidays.

For more information on syntax standards, see Language Documentation Syntax Notes.

date1, date2

Date values can be column references or output of the DATE function or the TIME function.

Date values to compared in working days.

  • If date2 > date1, then results are positive.
  • If date2 < date1, then a null value is returned.

If date1 and date2 have a specified time zone offset, the function calculates the difference including the timezone offsets.

  • If date1 does not have a specified time zone but date2 does, the function uses the local time in the same time zone as date2 to calculate the difference. The functions returns the difference without the time zone offset.

Usage Notes:

Required?Data TypeExample Value
YesDatetime (Column reference or date output of DATE or TIME function)LastContactDate

str_workingdays

A seven-character string identifying the days of the week that are working days.

  • String value must be seven characters long and contain only 0 or 1 characters. All other values are ignored.
  • First character in the string represents Monday and last character in the string represents Sunday.
  • If the string is not specified, then a Monday - Friday workweek is used.

Examples:

str_workingdaysWeekend days
'0000011'Saturday and Sunday (default)
'1000011'Monday, Saturday, and Sunday
'0000000'None.


Usage Notes:

Required?Data TypeExample Value
YesArray['1000011']

array_holiday

An array containing the list of holidays, which are factored in the calculation of working days.

Values in the array must be in either of the following formats:

['2020-12-24','2020-12-25']
['2020/12/24','2020/12/25']

Usage Notes:

Required?Data TypeExample Value
YesArray['2018-12-24','2018-12-25','2018-12-31','2019-01-01']

Examples

Tip: For additional examples, see Common Tasks.

Example - Date diffing functions

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: 

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:

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

Your Rating: Results: 1 Star2 Star3 Star4 Star5 Star 2 rates

This page has no comments.