Using DIFFINDAYS to auto calculate age of a contact – Dynamics CRM 2015 Online Update 1

First of all, from the title this seems trivial isn’t it. After all as CRM consultant we do so many complex stuffs and calculating the age of a contact is just so simple!

However the point I wanted to highlight here is to use the DIFFINDAYS and host of other functions introduced in Business Rules editor and what can be more interesting than demoing it by automatic calculation of age which is so often required.

First things first. Fields like ‘Age’ of a contact or anything which increases year or year should be calculated and how many times I have seen in implementations that this field is set to numeric with free text entry. So please remember that if you have any requirement like this, please mark the field as read-only and it should be calculated based on some other field value.

So we have made the field read-only. If you are thinking wouldn’t it be nice if this field is auto calculated also, then you are on the correct way pal.

So we make the field auto calculated. We can set up this field and make it a calculated field. I have done the same. Please check for the screenshot below.

image

Please note here that I have kept the age field as Decimal because my requirement was to show in fraction of years. However you can keep this whole number also.

Now comes the fun part. I click on edit and the editor opens up. I set the age as show in the screen shot below. Please note that I have created another field called ‘Date of Birth’ based on which the age is calculated.

 

image

The two important functions you can notice here are DiffInDays and Now().

These functions have come up with the 2015 Online update 1 release.

Now you save and close out this field. Now when you enter the Date of Birth, the age calculates automatically.

image

 

So no more marking the field read-only and writing jscript/ plugin for calculating the age. All this is handled automatically once you set the field as calculated in CRM.

 

This is just one example. You can explore a host of other functions introduced and use it to your liking.

image

 

Hope this helps!

Advertisements

About Debajit
I am a Dynamics CRM Most Valuable Professional (MVP) with 10 years of experience in Microsoft .NET Technologies and 7 years of dedicated experience in Microsoft Dynamics CRM. I have worked with companies like Microsoft, SanDisk, PwC, TMF Group and have extensive experience of implementing complex CRM solutions from both offshore and client side. Currently the face of XrmForYou.com with significant experience in delivering corporate training on Dynamics CRM and have already delivered multiple projects to client through XrmForYou.com Author of multiple tools on codeplex including the 'Role Based Views' and 'CRM-Sharepoint Metadata manager & Attachment Extractor' which are available for commercial use under XrmForYou.com For consulting/ training, drop me a note at info@xrmforyou.com or visit our website www.xrmforyou.com

18 Responses to Using DIFFINDAYS to auto calculate age of a contact – Dynamics CRM 2015 Online Update 1

  1. Marina says:

    Isn’t it going to provide a static value?

    • Debajit says:

      Hi Marina,
      It is using Now() function to calculate the age. Now() always returns the current date and time. So you would always get the latest value and not some static value

  2. Robert says:

    I can’t get this to work as the system tells me I “can’t set the value Now(), which is of type dateTime, to type integer.

    • Debajit says:

      Hi Robert,

      Now() only works for date time fields with behavior user local. Please check the behavior of your datetime field.

      • Robert says:

        Thanks Debajit, I just found a post on the CRMUG forum indicating that you need to create a new field (i.e. new_DateofBirth) in order for the formula to work.

  3. Maria de Moraes says:

    Hi Debajit

    I can’t to creat a list with all my contacts that have birthday in this week for example. please help me how can I do this?

    • Debajit says:

      Hi Maria,
      Can you tell me your exact scenario. Isn’t what is mentioned in the blog not working for you? If not then where you are getting stuck?

      Please note that the business rule functions mentioned in this example is only available in CRM 2015 Online Update 1 and CRM 2016. In case you are using any version lower than this then you might need to implement a plugin/ workflow to achieve the same functionality instead of business rule.

  4. Oliver says:

    Will this will be a few days out after awhile – with leap years etc and the fractional value rounding up, the age will always be off from the true age.

    • Debajit says:

      Hi Oliver,

      Thanks for reading my post. This was an illustration on how you can meet this requirement. However as shown in the last screenshot of the post, you can use other functions and out of all of them, I feel DiffInMonths would give you the most accurate age. Leap years and all does not come into the picture at all if we are calculating with months. And we can safely use 12 as the dividing factor.

  5. Gjelbrim says:

    HI Debajit
    What will happen with year which have 366 days(Leap years) ??

    • Debajit says:

      Thanks for reading my post. This was an illustration on how you can meet this requirement. However as shown in the last screenshot of the post, you can use other functions and out of all of them, I feel DiffInMonths would give you the most accurate age. Leap years and all does not come into the picture at all if we are calculating with months. And we can safely use 12 as the dividing factor.

  6. Karla Keeney says:

    You can use DiffInDays but with 365.25 as the dividing factor. That will account for leap years (at least until the year 2100 which will not be a leap year).

  7. Karla Keeney says:

    I was able to do this with a birthdate field that was created in CRM version 1.2, and subsequently upgraded to versions 3.0, 4.0, 2011, and is now being upgraded to 2016 (migrating through 2013 and 2015 as part of that process). I did not have to create a new field.

    I used DiffInDays and divided by 365.25 — hoping there’s a built-in ‘calcAge’ formula before the year 2100 rolls around.

    • Debajit says:

      Hi Karla,

      Thanks for reading my post. This was an illustration on how you can meet this requirement. However as shown in the last screenshot of the post, you can use other functions and out of all of them, I suggest DiffInMonths would give you the most accurate age. Leap years and all does not come into the picture at all if we are calculating with months. And we can safely use 12 as the dividing factor.

      Regards
      Debajit

  8. malcolmmcauley says:

    Nice post, thanks for putting it up. I am looking for a way to identify the last day of the current calendar year. I need to set a date on a field as June 30, XXXX. Is there a way to use this method to achieve that without script of any sort? Essentially we want to be able to identify the last day of the current year so we can count forward six months or so. Thoughts?

  9. Govind Jaya Shouri says:

    Wouldn’t this work with DiffInYears() function directly? No need to worry about leap year and things like that with that I guess.

    • Debajit says:

      Hi Govind,
      DiffInYears wont work for all scenarios. For example, a person born on 1st May 2010, his/ her age would show 6 years even on 1st January 2016 if you use DiffInYears. However the best one to use is diff in months as leap year or not, this will always be constant – 12. Also when I wrote this blog, diffinmonths was unfortunately not available as one of operators. So I had to fallback to days.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: