Workflow Solution to Calculate Duration of Business Days in Dynamics 365 CRM

In my previous blogs I showed you how to use Workflows and Calculated fields to record the duration of stages and milestones for any given pipeline such as an opportunity within Dynamics CRM. The duration for such stages and events provides important MI which is useful in identifying performance efficiency and bottlenecks within a business process. However this approach was limited to calculating the actual elapsed duration, which did not take into consideration actual business operational hours, and therefore could possibly give misleading information regarding the duration of a given process when the business was closed such as a bank holiday or weekend.

In this  blog I would like to share my solution using Workflow Activity that allows you to calculate the actual business duration based on the start and end date time value parameters. My business duration solution allows you to define the business closures within Dynamics 365 as well as choose whether to include or exclude weekends in the duration calculation.

Define Business Closures

For the Business Duration Solution to work you will need to define the Business Closures In CRM / Dynamics 365 when you navigate to Settings > Business > Business Closures ;

Create a Business Closure Records  for your Business holidays such as Bank Holidays and any other public holidays specifying the Duration of the Closure or an All Day Closure.

*As explained in my blog you can record duration’s in a custom entity to record each duration using worklows.

In order to calculate the Business Duration You will be required to provide a Start and End date  as Input Parameters from your Records

Start: Ie Created On

End: Ie Completed

Business Duration Metrics: The Business Duration currently calculates the Business Duration in the following Metrics, and rounds the result to its closest whole number unit;

• Business Days
• Business Hours
• Business Minutes

Once you have downloaded and installed the solution

You will need to create a Workflow and set the ‘Record Fields Change’ = The Field the provides the Time for end of Duration

 Then add a new Step,  select the MSCRM.workflow menu and select the BusinessWorkingDays option;

Specify If you want to Exclude Weekends from the Duration Calculations, Normally this will be True unless your business operates over the Weekend.

Specify the Fields to be used for the Start and End Dates of the Duration Calculation. The Start date will Usually be the Createdon date and the End Date can be Modified which will incrementally update the Business Duration ( I would not recommend using the  modified field as the end date from a performance overhead perspective) or a Custom date Field that is updated by another workflow to confirm the stage has been completed which is the recommended approach as it will provide the correct duration value once the stage has completed.

Set the Business Duration for any of the following metrics, please ensure your Field Used for Duration is a Whole Number Field Type.

• Business Duration in Days – Rounds the Result to the Closest Day
• Business Duration in Hours – Rounds the Result to the Closest Hour
• Business Duration in Minutes – Rounds the Result to the Closes Minutes

Add an Update Step to set the Calculation Value of the Business Duration, by selecting the Local Value of Business Working Days Workflow Activity as Shown Below; 

Now you can set the Values for Business Duration in Days, Hours or Minutes;

You can now use your Business Duration results for Reporting, Workflows and SLAs.

 You can download my Business Duration Solution for Dynamics CRM  below;