How to calculate age in years and months in Excel

Years and months with DATEDIF

DATEDIF(start, end, unit) measures the gap between two dates. With "y" it returns complete years, so the age only goes up once the birthday has passed. With "ym" it returns the complete months left after those years, always 0 to 11. Join the two with & and some text and you have the label.

The as-of date sits in H1 (3 November 2026) and is locked with dollar signs so the formula fills down. Maya Chen was born on 14 March 1992. She turned 34 in March, and her 14 October "month birthday" has passed but 14 November hasn't, so she is 34 years and 7 months.

DATEDIF is a hidden function: Excel doesn't suggest it as you type and shows no argument tooltip, but it works in every current version. Type it in full.

A label that reads naturally

The basic formula gives "41 years, 0 months" for Priya Nair, whose birthday is today, and "0 years, 9 months" for Sam Okafor, born in January. For name badges, letters or a class list, you usually want "41 years" and "9 months", plus "1 year" rather than "1 years".

LET names the years and months once, so DATEDIF runs twice instead of in every IF. Each part is added only when it is needed, and a comma goes between them only when both are there. A baby under one month old still gets "0 months", so the cell is never blank.

Why not use "md" for the days

DATEDIF has an "md" unit for the days left over after complete months, and it is the obvious way to get "years, months, days". Microsoft's own DATEDIF page recommends against it, because it can return a negative number, a zero or an inaccurate result. The problem shows up when the birthday falls on a day the previous month doesn't have, such as the 29th to 31st.

Tom Becker was born on 31 January 2000. On 1 March 2026 he had just passed his 26th birthday plus one month (February has no 31st, so his month birthday fell on 28 February). His real answer that day is 26 years, 1 month, 1 day, but "md" returns a negative number. On most dates "md" gives the right answer, which is exactly why the bug slips into finished reports.

Days the safe way: EDATE

Count the complete months with DATEDIF "m", jump forward that many months from the birth date with EDATE, and subtract that date from the as-of date. What is left is the number of days since the last month birthday, and it can never be negative. EDATE handles short months by moving to the month's last day, so Tom's month birthday in February is the 28th.

On 3 November 2026 Tom has had 321 complete months since birth. EDATE lands on 31 October 2026, three days before the as-of date. Put the three parts together and the full label is =DATEDIF(B5,$H$1,"y")&" years, "&DATEDIF(B5,$H$1,"ym")&" months, "&($H$1-EDATE(B5,DATEDIF(B5,$H$1,"m")))&" days".

If the result shows as a date like Jan 3, 1900, the cell picked up a date format from the subtraction. Set it to General or Number.

Keep the as-of date in one cell

You could write TODAY() inside every formula, but a fixed as-of cell is better for most real sheets. The ages on a printed register or an exported report stay as they were on that day. You can answer "how old will they be on 1 September?" for a school or club cut-off by changing one cell. And when you want live ages, type =TODAY() into H1 and every formula follows.

It also makes birthdays easy to check. Leo Alvarez turns 7 on 4 November, one day after the as-of date, so today he is 6 years and 11 months. Change H1 to 4 November and he becomes "7 years, 0 months".

Guard the formulas against a birth date after the as-of date, which DATEDIF answers with #NUM!. That happens with typos, with future dates, and with two-digit years: with default settings Excel reads 1/5/29 as 2029 but 1/5/30 as 1930. Wrap the label in =IF(B2>$H$1,"Check date",...) or IFERROR so a bad row stands out instead of breaking totals.

Which age to use: label, months or number

Use the text label for display: badges, letters, a member list. It is text, so it cannot be sorted or averaged sensibly; "10 years" sorts before "9 years". Sort by the date of birth instead (oldest first is ascending).

Use total months, DATEDIF with "m", when ages are compared in months: nursery rooms, under-2 groups, product age ranges. Use whole years, DATEDIF with "y", for age brackets, COUNTIFS bands and eligibility checks. For a decimal age you can average, DATEDIF "m" divided by 12 gives 34.58 for Maya. Excel's YEARFRAC also returns decimal years, but it counts days, so its decimals do not line up neatly with months.