Formula Calculate Age in Excel?

To calculate age in Excel, use the formula =DATEDIF(birthdate, TODAY(), "Y"), which returns the complete number of years between a birth date and today. You enter the cell containing the birth date (or the date itself), and the formula updates automatically each day.

Formula

The core formula is =DATEDIF(start_date, end_date, unit). In words: DATEDIF returns the difference between two dates in the unit you specify. For age in complete years, the unit is "Y".

• start_date – the birth date (a cell reference like A2 or a date entered with DATE(1990,5,15)).
• end_date – the date to measure up to; usually TODAY() for a live age.
• unit – "Y" (years), "YM" (months since last birthday), "MD" (days since last month boundary), or "D" (total days).

For example, =DATEDIF(A2, TODAY(), "Y") gives the age in years as of today.

Worked Example

Suppose cell A2 contains 2000-01-15 and today is 2024-03-10. The formula =DATEDIF(A2, TODAY(), "Y") calculates:

1. Count complete years from 2000 to 2024 → 24 years.
2. Check if the anniversary (Jan 15) has passed this year – yes, since March 10 is after Jan 15.
3. Result: 24 years.

If today were 2024-01-10, the result would be 23 because the 24th birthday hasn’t occurred yet. DATEDIF counts only complete years, not rounded years.

How to Choose the Right Unit

DATEDIF is flexible; you can get different levels of detail:

• Years only – "Y" gives a clean integer age.
• Years and months – combine "Y" and "YM" in separate cells: =DATEDIF(A2,TODAY(),"Y") and =DATEDIF(A2,TODAY(),"YM").
• Full breakdown – add "MD" for days since the last month boundary.
• Total days – "D" gives the exact number of days, useful for precise age in days.

For most purposes, "Y" is what people mean by “age.� If you need an age in decimal years (e.g., 24.5), use =YEARFRAC(A2,TODAY()) instead.

Common Mistakes and How to Avoid Them

• Using YEAR() incorrectly – =YEAR(TODAY())-YEAR(A2) gives the difference in calendar years, not age. Someone born in December 2000 would show 24 in January 2024 even though they are still 23.
• Forgetting DATEDIF is hidden – DATEDIF does not appear in Excel’s function list, but it works if typed manually. It is a legacy function from Lotus 1-2-3.
• Date stored as text – If the birth date is text like “15/01/2000�, DATEDIF returns #VALUE!. Convert it to a real date using =DATEVALUE().
• Using TODAY() in a static report – TODAY() recalculates every time the workbook opens. If you need a fixed age as of a certain date, use that date in a cell instead.

Alternatives to DATEDIF

If you prefer not to use DATEDIF, or if you need a different kind of age:

• YEARFRAC – =YEARFRAC(A2,TODAY()) returns age as a decimal (e.g., 24.5). Good for actuarial or statistical work.
• INT(YEARFRAC(…)) – wraps YEARFRAC to get the integer age, but it can produce off-by-one errors due to rounding in some date systems.
• DATEDIF with “Y� – the most reliable for whole years, as it follows the calendar exactly.

Why DATEDIF Sometimes Returns Errors

The most common error is #NUM!, which appears when the start date is after the end date. For example, if you accidentally put today’s date as the start and the birth date as the end, DATEDIF fails because it expects the first date to be earlier. Always check that your birth date is in the past.

Another issue is regional date formats. If your Excel uses day-month-year and you enter a date as month-day-year, the result may be wrong or produce an error. Use DATE(2000,1,15) to avoid ambiguity.

Frequently Asked Questions

Does DATEDIF work in all versions of Excel?

Yes, DATEDIF works in Excel 365, Excel 2019, 2016, and earlier versions, but it is not listed in the function wizard. You must type it manually. It also works in Google Sheets.

How do I calculate age in months or days?

Use =DATEDIF(A2,TODAY(),"M") for total months, or =DATEDIF(A2,TODAY(),"D") for total days. For months since the last birthday, use "YM"; for days since the last month boundary, use "MD".

Why does my age formula show 0 for someone born this year?

That’s correct – DATEDIF returns the number of complete years. A baby born in 2024 has not completed a full year yet, so the result is 0. If you want to show “0 years�, that’s fine; if you want months, use "YM" or "M".

Can I calculate age from a birth date in a different cell format?

Yes, as long as the cell contains a real Excel date (a number with a date format). If it’s text, use =DATEVALUE(A2) to convert it first. You can then use that converted cell in DATEDIF.

How do I freeze the age at a specific date (not today)?

Replace TODAY() with a cell containing the reference date, e.g., =DATEDIF(A2, B2, "Y") where B2 has a date like 2024-01-01. The age will not change unless you edit B2.

Is there a way to calculate age in years and months in one formula?

Yes, but you need to concatenate two DATEDIF results: =DATEDIF(A2,TODAY(),"Y") & " years, " & DATEDIF(A2,TODAY(),"YM") & " months". This returns a text string like “24 years, 2 months�.

Related

• Moving Quote Calculator by Zip Code: How It Works
• Lines of Code Calculator: Estimate Project Size
• Love Calculator Python Code: How It Works

in t | Birth date (as serial number) | - | 36526 | 0
in d | Reference date (as serial number) | - | 45292 | 0
out age | Age in years | years | DATEDIF(t, d, "Y") | 0
out months | Remaining months | months | DATEDIF(t, d, "YM") | 0
out days | Remaining days | days | DATEDIF(t, d, "MD") | 0