Calculate Age in Years and Months in Excel

To calculate age in years and months in Excel, use DATEDIF twice: once with "Y" for the whole years, and once with "YM" for the months left over. Join the two with & and you get a result like "36 years, 2 months". This guide shows the formula, how to base it on today's date, how to add days, and the mistakes that cause wrong ages.

The formula for age in years and months#

DATEDIF counts the time between two dates in the unit you choose. Its syntax is =DATEDIF(start_date, end_date, unit). For age, the start date is the birth date.

All examples use this sheet, with ages worked out on 2 November 2026:

In D2, type:

=DATEDIF(B2,C2,"Y")&" years, "&DATEDIF(B2,C2,"YM")&" months"

Amira's result is 36 years, 2 months. Fill down (Windows: Ctrl+D; Mac: ⌘D) and Leo gets 25 years, 7 months, and Hana 16 years, 9 months.

The & joins the numbers and the words into one piece of text. Note the spaces inside the quotes, so the result doesn't read "36years,2months".

What the DATEDIF units mean#

Each unit counts something different. These are the ones you need for age:

Amira turned 36 on 20 August 2026. Two more whole months take her to 20 October, so "YM" is 2. Her next month isn't complete until 20 November, so it doesn't count yet.

DATEDIF is an older function that Excel keeps for compatibility. It works in every version, but Excel doesn't suggest it as you type or show its arguments, so type the whole name yourself. The DATEDIF function guide lists every unit.

How to calculate current age from today's date#

Replace the end date with TODAY(), and the age updates every time the file opens:

=DATEDIF(B2,TODAY(),"Y")&" years, "&DATEDIF(B2,TODAY(),"YM")&" months"

On 2 November 2026 this returns the same 36 years, 2 months for Amira. A month later it shows 3 months. For reports that must not change, such as age at hire date, use a fixed date in a cell instead. The guide to calculating age and years of service covers fixed-date ages in more detail.

How to calculate age on a set date#

Many forms ask for age on a fixed day, such as the end of the year or the start date of a course. Put that date in the formula with DATE, or better, in a cell that everyone can see:

=DATEDIF(B2,DATE(2026,12,31),"Y")&" years, "&DATEDIF(B2,DATE(2026,12,31),"YM")&" months"

On 31 December 2026, Amira is 36 years, 4 months: two more whole months have passed since 20 October. Hana is 16 years, 11 months. Keeping the date in its own cell, such as C1, and pointing every row at $C$1 means you change one cell to recalculate the whole list.

How to add days to the age#

For years, months and days, add a third part. The "MD" unit works for most dates, but Microsoft warns that it can give wrong results in some cases, especially at the end of months. A safer way to count the leftover days is to step forward by whole months with EDATE and subtract:

=DATEDIF(B2,C2,"Y")&" years, "&DATEDIF(B2,C2,"YM")&" months, "&C2-EDATE(B2,DATEDIF(B2,C2,"M"))&" days"

For Amira this returns 36 years, 2 months, 13 days. DATEDIF(B2,C2,"M") is 434 months, EDATE moves 434 months on from her birth date to 20 October 2026, and 2 November minus 20 October is 13 days. Leo shows 18 days and Hana 3 days. For more on stepping dates forward, see how to add days, months and years.

How to get "1 year" instead of "1 years"#

Use IF to pick the singular word when a part equals 1:

=DATEDIF(B2,C2,"Y")&IF(DATEDIF(B2,C2,"Y")=1," year, "," years, ")&DATEDIF(B2,C2,"YM")&IF(DATEDIF(B2,C2,"YM")=1," month"," months")

For a baby born on 1 October 2025, this shows "1 year, 1 month" on 2 November 2026 instead of "1 years, 1 months". It is longer, but it reads naturally on a printed report.

Age as numbers instead of text#

Text like "36 years, 2 months" is easy to read but hard to sort or average. If you need to calculate with ages, keep them as numbers in separate columns:

• Years: =DATEDIF(B2,C2,"Y") returns 36.
• Months: =DATEDIF(B2,C2,"YM") returns 2.
• Total months: =DATEDIF(B2,C2,"M") returns 434. The article on months between dates explains this unit.
• Decimal years: =YEARFRAC(B2,C2,1) returns about 36.2.

Sorting by total months puts people in exact age order, even when two of them share the same number of years.

Common age formula problems#

Most wrong ages come from the dates, not the formula. Check these:

1. #NUM! error. The start date is after the end date. Put the birth date first.
2. An age of 126 years. The birth date cell is empty, so Excel reads it as early 1900. Use =IF(B2="","",your formula) to skip empty rows.
3. #VALUE! error. The birth date is text, not a real date. Real dates sit on the right of the cell by default; text sits on the left.
4. The result looks like a date. If you used a single DATEDIF and see something like 05/02/1900, change the cell format to General.

For a simple count of days instead of years and months, see days between two dates.

Practise it#

Date maths shows up in HR, finance and admin tests. Practise days between dates in a live spreadsheet: you work out real gaps between dates with subtraction and DATEDIF, and the lesson checks each formula instantly. Then try the exercise on this page to build the full "years and months" result yourself.