Dynamics 365 Case Management – calculating the number of days a Case has been open

When you create a Case in Dynamics 365 the Case has a Created On date which is useful for determining how old a Case is. However, what I find more useful is using a simple out of the box tool called Calculated Fields to calculate and display the number of days a Case has been open. The reason I prefer this is it’s a nice simple, but meaningful, visual for Customer Service Representatives and Customer Service Managers. They can use this field to help them quickly hone in on those Cases that have maybe been open for a while and understand why they are still open.

To setup this calculation I need to create two new fields:

  1. A ‘Today’ field which displays today’s date
  2. A ‘No. Days Open’ field which calculates the difference in days between today’s date and the Case Created On date

To create the fields I go to my solution and edit the Case entity.

I’m going to:

  • Add a new field called Today. 
  • Set the field type as Date and
  • Click on Add next to Calculated or Rollup and select Calculation 

Once I’ve done that I click on Edit to setup the calculation.  To calculate today’s date I enter Now() and click on the blue tick icon to save the calculation.

Calculation done, I save and close the form.

The next field I’m going to create is No. Days Open.  I’m going to:

  • Set field type as decimal
  • Click on Add next to Calculated or Rollup and select Calculation

I only want it to perform the calculation for Active Cases so in this calculation, I’ve added a condition which checks the Status of the Case.

Moving down the form to the calculation, I want to calculate the Difference in Days between Created On date field and the Today field. Once that’s done I click on the blue tick to save the calculation.

Calculation done, I can save and close the form.

Now that I’ve created the fields, I can add them to my Case form and publish my changes. When I navigate to a Case record there’s a section called Case Timescales and you can see the number of days this Case has been open.

Taking this one step further, I added this field to the Active Cases and My Active Cases view.  That means as soon as I load that view I can see a list of my Cases and the number of days that each one has been open. 

Now one thing to point out here is that the calculation does calculate total number of days i.e. including weekends/public holidays etc. This may not be ideal for everyone but personally, I still think this is a good enough metric to use to give you quick and meaningful insight into service performance.  

Leave a Reply

Your email address will not be published. Required fields are marked *