Description:

The Transform Date by Getting Working Days Between Two Dates step calculates the number of working days between two configured date columns. The transformation is configured through the Configure Dates property, where ETL designers specify the start date, end date, destination column, and any applicable working-day calculation options.

Many business processes measure elapsed time in working days rather than calendar days. For example, organizations frequently monitor SLA compliance, procurement cycles, invoice processing times, customer response times, onboarding durations, and project milestones based on business days. This transformation provides a reusable method for calculating these values without requiring custom SQL or scripting.

For each record processed, the ETL engine evaluates the configured start and end dates and calculates the number of working days between them. The calculated value is written to the configured destination column and becomes immediately available to downstream ETL components.

Unlike Transform Date by Extracting a Part, which derives components such as the year or month from a date, this transformation performs date interval calculations to determine business-day durations.

The Transform Date by Getting Working Days Between Two Dates step is typically placed after importing and validating date values but before filtering, reporting, aggregation, exporting, or loading the transformed dataset.

The step supports:

• Working day calculations
• Business-day duration measurement
• Multiple date calculations
• SLA reporting
• Timeline analysis
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

Typical business uses include:

• SLA compliance reporting
• Invoice processing duration
• Employee onboarding timelines
• Purchase order cycle analysis
• Customer support response metrics
• Project milestone tracking
• Regulatory processing reports

Usage:

The Transform Date by Getting Working Days Between Two Dates step is typically inserted after importing and validating date values but before reporting, grouping, filtering, exporting, or loading the transformed dataset into downstream systems.

During execution, the configured start and end dates are evaluated for each row. The calculated number of working days is written to the configured destination column, making the result immediately available for additional ETL processing.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Measuring SLA completion times
• Calculating employee onboarding durations
• Reporting procurement cycle times
• Tracking customer support resolution periods
• Measuring invoice approval timelines
• Producing project duration reports
• Building executive KPI dashboards

The calculated working-day values can then be filtered, grouped, aggregated, exported, or loaded into downstream reporting and business systems.

Prerequisite:

The ETL process engine service should be running to execute the ETL definitions.

Example:

Let’s build and execute the “clsTransformDateWorkingDaysDef” example.          

• Create a new ETL definition named “clsTransformDateWorkingDaysDef” and open it in designer mode. 
• Drag “clsInputCSV, clsTransformDateWorkingDays, and clsOutputCSV” steps to the canvas. 
• Connect the dots between the “clsInputCSV” and other steps, as shown above.
• Define a variable or a global to hold the virtual path.
• Click the "clsInputCSV" step to configure its "Required" properties. Provide a name for the step. Specify the path to the input CSV file on the application server. Select the ETL data schema from the drop-down list. Note: The schema's column names should match those in the input file. Click the Save button. Click here to learn about the ETL Data Schema Designer.

• Click the "clsInputCSV" step to view its "Optional" properties. Select the Show Schema button. The pop-up window displays the schema columns as shown below. This function helps to understand the schema at a glance.  

• Click the "clsTransformDateWorkingDays" step to configure its "Required" properties. Provide a name for the step. Click the button to configure the transform list, then click Save. 

• Click the "clsTransformDateWorkingDays" step to configure the transform list. A pop-up window will appear. Click the Add Row (+) button to insert an empty row. Select the start and end date columns from the drop-down list, and select the output date column. Click the Save button. You may configure multiple dates by using the Add Row button.  

• Click the "clsOutputCSV" step to configure its "Required" properties. Enter a name for the step. Enter the path to the output CSV file on the application server. Click the Save button. 

• Click the "clsOutputCSV" step to configure its "Optional" properties. Provide a name for the step. Provide a variable or global reference to store the virtual output path. Click the Save button. 

• The “Logging” configuration is necessary for documentation and also measures workflow progress and percent complete. This is achieved by configuring the step state and percent fields individually, as shown below. Configure the “Logging” using the following properties.

• Save the ETL definition, create a new ETL instance, and execute. Render the ETL instance. Click the ETL step to view its properties. The “clsTransformDateWorkingDays” step should calculate the working days between the two dates and write the result to the output file. The variable or global holds the output file’s virtual path, as shown below. The calculated working-day values are now available for SLA monitoring, reporting, dashboards, analytics, and downstream business processing.

Tips:

• Validate all date values before performing working-day calculations.
• Ensure the start date always precedes the end date unless reverse calculations are intentionally supported.
• Standardize business-day definitions across ETL processes to maintain reporting consistency.
• Preserve the original date columns for auditing and historical analysis.
• Verify organizational calendar requirements before implementing SLA calculations.
• Test calculations using representative production datasets and known business scenarios.
• Combine this transform with Validate Data, Transform Date by Format, Transform Date by Extracting a Part, Filter with Conditions, Group By Transform, Sorting Transform, JSON Output File, or SQL Output components to build comprehensive date-based ETL workflows.

Notes:

• Multiple working-day calculations can be configured within a single transformation.
• Source columns must contain valid date values.
• The original date values remain unchanged unless explicitly overwritten.
• Calculated working-day values immediately become available to downstream ETL components.
• Invalid date values or incomplete date mappings may result in ETL execution errors.
• The exact calculation follows the configuration provided in the Configure Dates property and the FlowWright ETL engine's working-day rules.
• This transform is commonly used for SLA monitoring, project management, procurement reporting, HR analytics, customer service metrics, ERP reporting, business intelligence, and enterprise compliance reporting.

Transform Date by Getting Working Days Between Two Dates vs. Transform Date by Extracting a Part:

Although both transformations work with date values, they serve different business purposes:

As a general guideline:

• Use Transform Date by Getting Working Days Between Two Dates when measuring business-day durations between two dates.
• Use Transform Date by Extracting a Part when individual date components are needed for reporting, grouping, or analytical processing.

Definition Sample:

You may download the sample ETL definition(s) from the link provided and later import them into your FlowWright ETL Designer.

Note: Verify and complete any missing configuration after importing the sample, including:

• Start and end date mappings
• Destination column configuration
• Working-day calculation settings
• ETL schema validation
• Downstream transformation mappings
• Destination component configuration
• Environment-specific settings

After verifying the configuration, save the ETL Definition before execution.

Click here to download the sample file.