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.
- 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.
- 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
- Create a Workflow for the Opportunity entity using the specifications in the image below
- Under Start When, select Record Field Calculating the Number of Days Between Two Dates with Workflo Changes and check Sold Date
- Set the Scope to organization so that this workflow runs for entire organization, if applicable
- 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
- 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
- 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.
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!