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!
Happy CRM'ing!
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?
Hi - Do you have crm 2015? If so, we can now do calculated fields and have a number of great date related operations:
https://powerobjects.com/blog/2014/11/17/calculated-fields-in-dynamics-crm-2015/
http://blogs.technet.com/b/lystavlen/archive/2014/11/20/calculated-fields-new-in-crm-2015.aspx
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.
Very good 🙂
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.
Is there any workaround to calculate number of business days i.e excluding holidays and weekends between two dates ?
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.
Great, Thanks for the help