How to accurately calculate age in years, months, and days in Excel or Power Automate (including leap years)?

Question

How to accurately calculate age in years, months, and days in Excel or Power Automate (including leap years)?

ali sajid 0

I’m working on a solution that needs to calculate a person’s exact age (years, months, and days) based on date of birth. This is required for internal HR and compliance reports.

I’ve tried using DATEDIF in Excel and similar date functions in Power Automate, but I’m running into issues with leap years and inconsistent month lengths. Results sometimes differ by a day or two depending on the method used.

Is there a recommended Microsoft-native approach (Excel formula, Power Automate expression, or Power BI DAX) that reliably handles leap years and returns age in years + months + days?

For quick verification of results, I’ve also been cross-checking against this online tool: Age calculaor

Any guidance or best practices would be appreciated.

0 comments

1 answer

Your answer

Answer 1

Use DATEDIF, provided you calculate the components separately. It correctly accounts for leap years and different month lengths:

=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"

Here, A2 is the date of birth and B2 is the date on which the age is being calculated, such as TODAY(). For example: =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days. DATEDIF is designed to calculate elapsed years, months, and days and handles leap years and varying month lengths. You should not calculate the three components independently using simple day or month arithmetic.

For Power Automate, there is no direct equivalent of Excel's DATEDIF that provides years, months, and days in one expression. One option is to calculate the completed years first, adjust for whether the birthday has occurred in the current year, then calculate the remaining months and days from the adjusted anniversary date. For example, you can first calculate completed years with an expression based on ticks() and the birth date, but because Power Automate's date functions do not provide a native DATEDIF, implementing the full calculation requires several expressions and handling of month-end and leap-year dates. For an HR/compliance solution, I would avoid simply dividing the difference in ticks by 365 or 365.25 because that does not produce an exact years/months/days age.

In Power BI DAX, a similar component-based calculation can be built using DATEDIFF together with DATE, YEAR, MONTH, and DAY, but DATEDIFF by itself should not be interpreted as an exact age in years, months, and days. The calculation should establish the person's most recent birthday/anniversary first and then calculate the remaining months and days from that date.

For your requirement, Excel DATEDIF is likely the simplest Microsoft-native solution and is appropriate for verification. If the same calculation must be performed in Power Automate or Power BI, it is better to implement the same anniversary-based logic rather than compare raw numbers of days. An online age calculator can be useful as a sanity check, but for an HR/compliance process I would make the Microsoft calculation the authoritative result and test it specifically against dates such as February 29, February 28, March 1, month-end dates, and birthdays around leap years.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin