Calculating ‘Days Since…’ in Dynamics CRM

A scenario I come across quite often when configuring Dynamics CRM is the need to show the Days Since something occurred or happened.  For example:

  • the number of days an opportunity has been at a certain sales stage (used to identify and measure Stalled Opportunities)
  • the number of days an unpaid invoice is overdue, or past due
  • the number of days a quote has been in Open status

So logically, we need Dynamics CRM to understand two things:

  1. the date when the thing occurred or happened (e.g. the invoice due date)
  2. the date today

because with these two dates, simply subtracting one from the other will derive the Days Since that we are looking for.

The date today can be easily derived using the Now() function

NOW()

however, plugging this into a calculated field using our simple “subtract one from the other” logic produces an error:

NowFunctionError

But don’t worry, this is where the DiffInDays() function comes to the rescue.


Step 1

  1. Create and name a new field in the entity you are working with (e.g. ‘Days Open’ field in the Quote entity)
  2. Set the Data Type to Whole Number
  3. Set the Field Type to Calculated
  4. Click Edit next to Field Type

DaysOpenField


Step 2

In the Calculated Field dialog box

  1. Add a Condition (e.g. If Status equals Active)
  2. Add an Action using the DiffInDays() function
DiffInDays({the thing that happened},NOW())

So that the calculated field ends up looking something like this:

CalculatedFieldSetDaysOpen2

and that’s it.  Then:

  1. Click Save and Close  
  2. Click Save and Close

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s