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.
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.
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.
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.
Hope this helped!
Debajit Dutta
Business Solutions MVP
Isn’t it going to provide a static value?
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
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.
Hi Robert,
Now() only works for date time fields with behavior user local. Please check the behavior of your datetime field.
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.
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?
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.
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.
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.
HI Debajit
What will happen with year which have 366 days(Leap years) ??
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.
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).
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.
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
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?
Sorry for the delayed reply as I am mostly on the road this week. I will get back to you on this very quickly.
Wouldn’t this work with DiffInYears() function directly? No need to worry about leap year and things like that with that I guess.
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.
First of all, thanks for the wonderful post.
Better that you used diffindays which is most accurate as compared to months and years.
Diffindays will be the closest to the accuracy to 1 day span.
Diffinmonths will be closest to the accuracy to 1 month span.
Diffinyears will be closest to accuracy to 1 year span.
e.g. if you use diffinmonths, then person with birthday on 28-feb-2000 and another with birthday on 15-Feb-2000, both of them will show the age as 18 as soon as NOW() hits 01-Feb-2018.
Hi Anubhav, Thanks for reading my blog post. Yes DiffInDays would give the most correct. However still it is not accurate I know. But this it closest you can get using customisation ways.
Hi Debajit
Your solution sounds good by we’ve still customers who are not happy.
So first of all field birthdate must be DateOnly-DateOnly this means no timezone information.
What do you think does it make sense for you to have birth date file UserLocal?
I always expect my birthdate to be on 16 of Aprill no matter what TimeZone I’m using.
If we use DateOnly behavior for birth date field then we are not able to use functions that you mentioned in your solution such DiffInDays or DiffInYears.
Please if you’ve any good solution would be very helpful.
GJ
Hi GJ,
Unfortunately you cannot use the now() function in business rules for datetime fields other than userlocal.
-Debajit
Hi There,
I have implemented this, however the field will automatically round to the nearest whole number which doesn’t give an accurate reflection of age, ie if someone is 30 years and 7 months they are still 30 years old. am i missing something obvious
Hi Scott,
this example was written when DiffinMonths was not there. You can use Diff In Months for more acccurate result
-Debajit
Hi Debajit, on reflection and more testing i still have the same issue. how would you ensure the field value always rounds down?
Hi Debajit,
thanks for the prompt reply, thats resolved the issue. thanks again!!!
Great post, thank you.
How do I show years only? The age field shows 31.58, I want to to show 31, however it rounds up to 32 when 0 decimals are selected.
Thanks
Hi Heidi,
Thanks for reading my blog. This is the problem when we chose a whole number field. Since anything more than 5+ in decimal, it will round off to the next number.
Also the calculation is not exact when we do it for DIFF in days and leap years are there. But this is closest approximate u can get with OOB.
-Debajit