I recently encountered an issue when trying to create an Exact Age column for a contact in Microsoft Dynamics CRM. There were several solutions available on the internet, but none of them was a good match for my specific situation. Some ideas I explored included:
- Creating a calculated field using the formula
DiffInDays(DOB, Now()) / 365 or DiffInYears(DOB, Now())– I used this at first, but if the calculated field is a decimal type, then you end up with a value like 23 years old which is not desirable. If the calculated field is a whole number type, then the value is always the rounded value. So, if the DOB is 2/1/1972 and the current date is 1/1/2019, the Age will be 47 when the contact is actually still 46 until 2/1/2019.
- Using Workflows with Timeouts – This approach seemed a bit complicated and cumbersome to update values daily across so many records.
Determining Exact Age
Instead, I decided to plug some of the age scenarios into Microsoft Excel and simulate Dynamic CRM’s calculations to see if I could come up with any ideas.
Note: 365.25 is used to account for leap years. I originally used 365, but the data was incorrect. After reading about leap years, I decided to plug 365.25 in, and everything lined up.
Setting up the formulas above, I was able to calculate the values below. I found that subtracting the DATEDIF Rounded value from the DATEDIF Actual value produced a negative value when the month/day was after the current date (2/16/2019 at the time). This allowed me to introduce a factor of -1 when the Difference was less than or equal to 0. Using this finding, I set up the solution in CRM.
- Create the necessary fields.
Field Data Type Field Type Other Formula DOB Date and Time Simple Behavior: User Local Age Actual Decimal Number Calculated Precision: 10 DiffInDays(new_dob, Now()) / 365.25 Age Rounded Whole Number Calculated DiffInDays(new_dob, Now()) / 365.25 Age Difference Decimal Number Calculated Precision: 10 new_ageactual – new_agerounded Age Whole Number Calculated See below
- Create a business rule for DOB; setting it equal to birthdate when birthdate contains data. This way when birthdate is set, the DOB is set automatically. This arrangement is necessary for other calculated fields.
- Set up the Age calculated field as follow:
Once these three steps have been completed, your new Age field should be ready to use. I created a view to verify the calculations. I happened to be writing this post very late on the night of 2/16/2019. I wrote the first part before 12:00 a.m., then I refreshed the view before taking the screenshot below. I was happy to see Age Test 3 record flip from 46 to 47 when I refreshed after 12:00 a.m.
Determining Exact Age at Some Date in the Future
The requirement that drove my research for this solution was the need to determine the exact age in the future. Our client needed to know the age of a traveler on the date of travel. Depending on the country being visited and the age of the traveler on the date of departure, different forms would need to be sent in order to prevent problems when the traveler arrived at his or her destination. The solution was very similar to the Age example above:
- Here is an overview of the entity hierarchy:
- Create the necessary fields.
Entity Field Data Type Field Type Other Formula Trip Start Date Date and Time Simple Behavior: User Local Contact DOB Date and Time Simple Behavior: User Local Trip Contact Age at Travel Actual Decimal Number Calculated Precision: 10 DiffInDays(contact.dob, new_trip.start) / 365.25 Trip Contact Age at Travel Rounded Whole Number Calculated n/a DiffInDays(contact.dob, new_trip.start) / 365.25 Trip Contact Age at Travel Difference Decimal Number Calculated Precision: 10 new_ageattravelactual – new_ageattravelrounded Trip Contact Age at Travel Whole Number Calculated n/a See below
- Create a business rule for Contact DOB; setting it equal to birthdate when birthdate contains data. This way when birthdate is set, the DOB is set automatically. This arrangement is necessary for other calculated fields.
- Set up the Trip Contact’s Age at Travel calculated field as follow:
Once these steps have been completed, your new Age at Travel field should be ready to use. I created a view to verify the calculations.
You’ll notice that in the red example, the trip starts on 8/14/2020. The contact was born on 9/29/2003 and is 16 on the date of travel but turns 17 a month or so later. In the green example, the trip is also on 8/14/2020. The contact was born 4/12/2008 and will turn 12 before the date of travel.
While there are several approaches to the Age issue in Dynamics CRM, this is a great alternative that requires no code and works in real time. I hope you find it useful!