Difference between two dates with calculated fields

The ability to add new fields to Microsoft Dynamics CRM is part of what makes it so powerful. Anything new you want to capture, you can add fields using a variety of field types. Calculated fields are extremely powerful, allowing you create new fields with powerful conditions as needed. In this example, let’s make it easy to see how long it’s been since there was any contact made regarding the Opportunity. We will use the Rollup field created in a previous post here. The Data Type will be Whole Number, and Field Type is Calculated. Once Calculated is selected, an Edit button is available.

Once the Edit button is clicked, your criteria can then be set. Set the condition first. In this instance, we want to make sure the Last Contacted field has a value. Next, if you add an Action, you can start typing DIFFINDAYS. This will determine the difference in days between the next two dates we pick. You can see that by typing in the word ‘last’, the fields available are filtered. We can then select ‘new_lastcontacted’.

To calculate how long it’s been since the Opportunity last had contact, we can use NOW to determine today’s date. This will then allow us to determine the number of days between the last time anyone was contacted regarding the Opportunity and today.

We can then go a step further and add a condition if there hasn’t been any contact and the Last Contacted date field is empty. This time we can calculate the number of days between the Created On date of the Opportunity and Today.

Finally, we add the new field to the Opportunity form below the Last Contacted Date field. We can see here, there has been no contact at all yet, and the Opportunity was created 20 days ago from today.

Once we have an activity regarding the Opportunity, we can then see how long ago it’s been since today in number of days.