PowerObjects Blog 

for Microsoft Business Applications


Calculating the Number of Days Between Two Dates with Workflow


Calculating the Number of Days Between Two Dates with Workflow

If you need to calculate the number of days between two dates, then there are a couple of options for you. There is a function called DATEDIFF being released with Microsoft Dynamics CRM Online Update 1 in Spring of 2015. If you have not yet received that update, then this blog will describe how to achieve this with workflow.

In this solution, we will use the native calculation of the duration on an Activity record and copy that duration to the calculated field on the target record.

Note: Experience with creating custom fields, entities, and workflows is required for this solution. Here's how we do it.

  1. Create the necessary fields to track the dates and the date difference calculation. In this example, we will calculate the number of calendar days an opportunity was openOn opportunity, we will be using an out-of-the-box field called Created on and a custom fields called Sold date. We are also creating a custom field called Sale Cycle to capture the number of days opportunity was open. The calculation we want to make is

    Sold date – Created = Sales cycle.

    We have created the Sales Cycle field with a Data Type of Whole Number and a Format of Duration.

    Calculating the Number of Days Between Two Dates with Workflow

  2. Create a new entity and check the box for 'Define as an activity entity' and publish changes. No other fields are required on this record

    Calculating the Number of Days Between Two Dates with Workflow

  3. Create a Workflow for the Opportunity entity using the specifications in the image below

    Calculating the Number of Days Between Two Dates with Workflow

  4. Under Start When, select Record Field Calculating the Number of Days Between Two Dates with Workflo Changes and check Sold Date
  5. Set the Scope to organization so that this workflow runs for entire organization, if applicable
  6. Insert a step into the workflow, to create a Number of Days record and set properties to set the Start Date = Created On and the Due Date = Sold Date

    Calculating the Number of Days Between Two Dates with Workflow

    Calculating the Number of Days Between Two Dates with Workflow

  7. Add a step to the workflow to update the Sales Cycle field on the Opportunity with the Scheduled Duration field on the Number of Days record

    Calculating the Number of Days Between Two Dates with Workflow

    Calculating the Number of Days Between Two Dates with Workflow

  8. Save the workflow and Activate it. That's all folks! Now whenever a user enters value in 'Sold date', this workflow shall run and provide the number of days between Sold Date and Created on date.

    Calculating the Number of Days Between Two Dates with Workflow

The example above used the field format as Date and Time, we can keep it simple by keeping the fields format as Date Only, as well.

Make sure to stop by our main Dynamics CRM 2015 page for even more great tips and tricks!

Happy CRM'ing!

 

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

8 comments on “Calculating the Number of Days Between Two Dates with Workflow”

  1. Is there any way to calculate the Number of years and not days? Also, if we need this to be dynamic and set workflow to start when record is created, should we create a function to delete all days/age calculations?

      1. Hi, no we have CRM 2013 online. Calculating either the year difference or the days difference was not a feature we could easily have. So we created a process over scribe calculating and updating all the necessary Information into CRM.

  2. is there a way in MS CRM 2015 to calculate a future date. For example based upon a contract date field we want to auto calculate a due date that is 45 days from sign date. I attempted with a calculated field by using the formulat "=cc_datesigned+ADDDAYS(45) but the formula is invalid. Date signed is a date field and the calculated field is a date field. Thanks.

  3. Is there any workaround to calculate number of business days i.e excluding holidays and weekends between two dates ?

    1. Currently there is no way in CRM to exclude holidays or weekends with a workflow. You could always custom code a workflow step to handle this. Also, there is a tool called the CRM Manipulation Library available at the CodePlex site that should help business day scenario.

PowerObjects Recommends