Skip to main content

Notifications

Announcements

No record found.

Community site session details

Community site session details

Session Id :
Microsoft Dynamics 365 | Integration, Dataverse...
Suggested answer

Age Calculation based on Date of Birth field - Solution

(9) ShareShare
ReportReport
Posted on by 34

 

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.

pastedimage1617026775592v1.png

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

pastedimage1617026784899v2.png

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

pastedimage1617026794020v3.png

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

pastedimage1617026801919v4.png

 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

pastedimage1617026807029v5.png

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

pastedimage1617026815120v6.png

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

pastedimage1617026822159v7.png

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

pastedimage1617026828052v8.png

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

pastedimage1617026833626v9.png

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

pastedimage1619720175447v1.png

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

pastedimage1619720235169v2.png

Advanced List View for some test records:

pastedimage1619720370455v1.png

  • Suggested answer
    CB-23120114-0 Profile Picture
    CB-23120114-0 7 on at
    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.
  • su_kamal Profile Picture
    su_kamal 17 on at
    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.
  • dbeebe Profile Picture
    dbeebe 6 on at
    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.


  • LuHao Profile Picture
    LuHao 40,876 on at
    RE: Age Calculation based on Date of Birth field - Solution

    Thanks for your sharing.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,468 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,287 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans