Dynamics Community Forum Thread Details

Age Calculation based on Date of Birth field - Solution

Age calculation is the most basic but a critical requirement within any system. As we already know it can be very complicated and a solution was not easily achievable in MS Dynamics 365 OOTB. After reading and researching a lot of blogs and customization's recommended on the internet, there was nothing that I could find which covered all the use cases like leap years, current day but different year birthday etc.

In my first attempt to solve a problem within D365, I came up with something that works for most if not all edge cases to calculate Age based on a user entered Date of Birth. In this solution we would need at least 4-5 calculated fields which can be created and configured by following this document.

1. Create a "DOB" field with Date and Time type but User Local behavior and Date Only format as shown below.

2. Create a Whole number type calculated field called "Number of Leap Years"

Set the formula as shown below to calculate the number of leap years since DOB till current year

3. Create a Whole number type calculated field called "DiffInDays".

Set the formula shown below for the field to accurately count the number of days from DOB till current day. Please note that we are removing the number of leap year days which is critical in our Age calculations

4. Please note that you may be able to perform step #2 calculations within step #3 if needed to reduce the number of custom fields.

5. Create a Decimal Number type calculated field "Decimal Age" as shown below

Please set the formula to calculate decimal age for the contact with 5 precision

6. Create a Whole Number type calculated field "Calculated Age" as shown below

Set the Calculated Age = Decimal Age as shown below. The reasoning for this step is to round up or round down the decimal age calculated in step # 5. As this field is of type Whole Number, D365 will automatically round up or round down the decimal age