Microsoft Dynamics 365 | Integration, Dataverse...

Age Calculation based on Date of Birth field - Solution

(12) Share

Report

Posted on by Deepak Agrawal

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

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

Setup the formula for this field which will calculate the Age for a Contact record

Advanced List View for some test records:

I have the same question (0)

All responses (5)

Answers (0)

TM-07052204-0 10 on at

Like (1)

Report
Age Calculation based on Date of Birth field - Solution

I am using D 365 Sales

Trying to calc age

DIFFINYEARS(birthdate, NOW())

you can't use NOW() which is of type datetime with the current function

Tried
RoundDown((DIFFINYEARS(birthdate, UTCToday())),0)

UTCToday() is not a function

Today() is not a function

Was this reply helpful? Yes No
Suggested answer

CB-23120114-0 9 on at

Like (1)

Report
Age Calculation based on Date of Birth field - Solution

For anyone looking for an updated answer to this post, the new Fx Formula fields that are offered in Power Apps gives a lot of creative freedom for a field like this. To calculate age, create a column with Data Type = Whole Number fx.

Enter the following formula:

RoundDown((DateDiff(Birthday, UTCToday())/365.25),0)

This takes into account leap years.

Please let me know if you have any issues with this! So far, it has worked great for me and my team.

calculated_age.png

Was this reply helpful? Yes No
su_kamal 17 on at

Like (2)

Report

Age Calculation based on Date of Birth field - Solution

Thanks for the guidance.

I skipped the step where it requires leap year.

I used this successfully for calculating "client contract age". I converted the decimal to 1 place to use for Years and Months.

Was this reply helpful? Yes No
dbeebe 6 on at

Like (2)

Report

Age Calculation based on Date of Birth field - Solution

Awesome solution! I've been looking for something like this. I have one concern/question. We converted the built-in "birthdate" field behavior property to "Date Only" instead of "User Local." Attempting to use the field in the child calculation fields throws an error when trying to use the Now() function. The reason we converted the field is because User Local will convert the birthdate based on the timezone setting of the CRM User -- which we don't want. The birth date is the same regardless of timezone. Why do the calculation throw this error for the Date Only format is used?

We are running Dynamics 365 v8.2 on premise.

Was this reply helpful? Yes No
LuHao 40,890 on at

Like (2)

Report

RE: Age Calculation based on Date of Birth field - Solution

Thanks for your sharing.

Was this reply helpful? Yes No