Calculate the Number of Days Between Two Dates

When analyzing data you may need to calculate the number of working days between two dates - for instance to understand whether a Service Level Agreement was met.

The Calculate Workdays node calculates the number of workdays between two specified dates and excludes weekend days and holidays (Public and/or personal time off).

You may already be familiar with the Excel NETWORKDAYS.INTL() function (or the slightly less flexible NETWORKDAYS() function) - the Calculate Workdays node provides a range of functions similar to the  NETWORKDAYS.INTL() function. In the following example the function is used in the cells in column D to calculate the number of whole days between the date values in the 'Created Date' field and the 'Date' field. Personal time off holidays are not specified but a range of cells in column F specify the US Public holidays for a number of years.

In Data3Sixty Analyze, the corresponding data flow would look like this:

The Test Dates data includes fields containing the start and end date values. In this case the values are string data type (i.e. as would be the case if the value was imported from a CSV file) and the date format is month first, as used in the USA. 

Similarly, the US Holidays data contains string representations of Public holidays for several years.

You can specify the date format to be used when the Calculate Fields node parses the input data but in this example the Modify Fields node is used to convert the two data sets to date type:

The Locale property was specified to indicate the use of the en_US locale rather than the system default for the machine.

The resulting Test Dates data looks like this:

The Test Dates data is connected to the (first) pin of the Calculate Fields node and the US Holidays data is connected to the optional second pin on the node. The node's 'StartDate' property is configured to use an input field as its source (rather than a literal value) and the 'Created Date' field is set as the name of the field containing the start date values. The 'EndDate' property is similarly configured to identify the input 'Date' field as the field containing the end date values for the calculation. 

As Public holidays are to be excluded from the calculation, the 'PublicHolidays' property is configured to obtain the data from the 'Holiday' field. Note that if holiday information is sourced from an input pin the data *must* be presented on the node's second input pin. As no personal time off days are to to be specified the 'PrivateHolidays' property is not set.

By default, the node assumes Saturday and Sunday are weekend days, but if you are working with data from jurisdictions where other weekend days are observed, you can set the 'WeekendCode' property. The 'WeekendCodeFormat' property enables you to specify the weekend code as a weekend code indicator, a weekend code bitmap or day of week values - see the node documentation for details.

When the node is run the calculated number of working days is output along with the start and end date values (if required you can set the name of the field containing the working days values ('_WorkdaysCount') using the 'OutputFieldName' property or, per the example, use the Modify Fields node to rename the field).

As can be seen from the results the calculated number of working days aligns with that obtained from the Excel NETWORKDAYS.INTL() function.

Attached is a data flow that contains this example. You will need to v3.4.0 or above to import the data flow.

You can find information on the node for your version of Analyze in the integrated help (see Node help > Profiling and Patterns > Calculate Workdays). Alternatively, you can view the help for the latest version of the product here:

https://d3sa-preview.infogixsaas.com/docs/dist/help/Content/e-node-help/Profiling_and_Patterns/calculate-workdays.html

An "Example Calculate Workdays Node" LNA that provides a number of use cases is provided with your installation in the following location:

<installation directory>/docs/samples/nodes

If you do not have access to the above location, please contact your administrator.

You can import this example data flow into your installation by using the Import Data Flows/Library Nodes command in the Directory

P.S.

If you just want to calculate the total number of days between two dates then the simplest method is to use the following expression in the Calculate Fields node:

You can set the default value to be used for missing date values - see the help for the 'DefaultValueForNullDate' property.