How to Calculate Age in Excel Using Formulas and Functions

The simplest way to calculate age in Excel

The fastest way to calculate someone's age is to use the DATEDIF function, which measures the time between two dates. If you have a birth date in cell A2, the formula =DATEDIF(A2,TODAY(),"Y") will show their age in complete years. The TODAY() function automatically uses today's date, so the age updates every day without you having to change anything.

DATEDIF takes three pieces of information: the start date (birth date), the end date (today), and what unit you want to measure in. The "Y" means years. You can also use "M" for months or "D" for days if you need a more precise measurement — for example, =DATEDIF(A2,TODAY(),"M") shows total months of age.

This function works in Excel on Windows, Mac, and in Google Sheets. It's the method most people use because it's short, reliable, and handles leap years automatically.

Key Takeaways

DATEDIF is the standard Excel function for age calculation and requires only the birth date and TODAY() to work.
The formula =DATEDIF(A2,TODAY(),"Y") calculates age in years; change "Y" to "M" or "D" for months or days.
If DATEDIF doesn't work in your version of Excel, use =INT((TODAY()-A2)/365.25) as an alternative.
You can calculate age on a specific date other than today by replacing TODAY() with a date in quotes, like =DATEDIF(A2,"2025-12-31","Y").

Setting up your spreadsheet for age calculations

Start by putting birth dates in one column — usually column A — with a header like "Birth Date" in cell A1. Make sure the dates are formatted as actual dates, not text. If you paste dates from another source and they appear left-aligned instead of right-aligned, Excel is treating them as text and the formula won't work. To fix this, select the column, right-click, choose Format Cells, and set the format to Date.

Put your age formula in the next column, usually column B. Click on cell B2 (the first data row below your header) and type =DATEDIF(A2,TODAY(),"Y"). Press Enter. The age will appear. Then click B2 again and drag the small square at the bottom-right corner of the cell down to copy the formula to all other rows with birth dates.

If you're calculating age as of a date that isn't today — for a historical record or a specific event — replace TODAY() with your target date in quotes. For example, =DATEDIF(A2,"2024-12-25","Y") calculates how old someone was on Christmas 2024.

When DATEDIF doesn't work: the alternative formula

Some older versions of Excel don't recognize DATEDIF, or it may not be available in your regional version. If you get an error, use this formula instead: =INT((TODAY()-A2)/365.25). This divides the number of days between the birth date and today by 365.25 (accounting for leap years) and rounds down to a whole number.

This formula is less precise than DATEDIF because it doesn't account for the exact calendar — it just uses an average year length. For most purposes it's close enough, but it can be off by a few months if someone's birthday hasn't occurred yet this year. DATEDIF is more accurate because it counts actual calendar days.

If you need months and days as well as years, stick with DATEDIF if possible. The alternative formula gets complicated for anything beyond years.

Calculating age in years, months, and days

Sometimes you need a more detailed age — for example, "3 years, 7 months, 14 days" instead of just "3 years". You can build this with three DATEDIF formulas in separate cells, or combine them into one cell with text.

To show all three in one cell, use: =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days". The & symbol joins text and numbers together. The "YM" means months after the last birthday, and "MD" means days after the last month-day anniversary.

This is useful for medical records, daycare enrollment, or any situation where approximate age isn't precise enough. The formula is longer but works the same way — it just runs three separate DATEDIF calculations and combines them with labels.

Handling dates that might be invalid or missing

If some cells in your birth date column are empty or contain text instead of dates, your formula will show an error like #NUM! or #VALUE!. To prevent this, wrap your DATEDIF formula in an IFERROR function: =IFERROR(DATEDIF(A2,TODAY(),"Y"),""). This tells Excel to show nothing (empty cell) if the formula fails instead of displaying an error.

You can also replace the empty quotes with text like "Invalid date" or "N/A" so you know which rows have a problem: =IFERROR(DATEDIF(A2,TODAY(),"Y"),"Invalid date"). This makes it easier to spot and fix bad data before you use the spreadsheet for anything important.

If you're working with a large list of dates, scan the column first to make sure they're all formatted consistently. Dates from different sources sometimes come in different formats — some as "01/15/1990", others as "January 15, 1990" — and Excel may not recognize all of them as dates.

Common mistakes and how to fix them

The most common error is forgetting the comma between the date and the unit. =DATEDIF(A2 TODAY() "Y") won't work — it needs to be =DATEDIF(A2,TODAY(),"Y") with commas separating each part. Excel will tell you there's a syntax error if you leave them out.

Another mistake is using the wrong unit code. The codes are case-sensitive and must be in quotes: "Y" for years, "M" for months, "D" for days, "YM" for months ignoring years, and "MD" for days ignoring months. If you type "years" instead of "Y", the formula fails.

If your birth date is in the future (for example, you're calculating age for an unborn child or a fictional character), DATEDIF will show a negative number or an error. This is correct behavior — the formula is working, but the date is invalid for age calculation. Make sure your birth dates are in the past.

Frequently Asked Questions

Can I calculate age from a date other than today?

Yes. Replace TODAY() with any date in quotes. For example, =DATEDIF(A2,"2020-01-01","Y") calculates how old someone was on January 1, 2020. Use the format YYYY-MM-DD or your local date format in quotes.

Why does my age calculation show a different number than I expected?

DATEDIF counts complete years, so if someone's birthday hasn't happened yet this year, they're still the age from last year. For example, if today is January 5 and someone was born January 10, 1990, they're still 34, not 35. This is the correct behavior for age calculation.

Can I use DATEDIF in Google Sheets?

Yes, DATEDIF works the same way in Google Sheets. The formula =DATEDIF(A2,TODAY(),"Y") calculates age identically. Google Sheets also supports the alternative formula =INT((TODAY()-A2)/365.25) if you prefer.

What if the birth date column has text that looks like a date?

Excel won't recognize it as a date, and DATEDIF will fail. Select the column, go to Data menu, choose Text to Columns, and click Finish. This converts text that looks like dates into actual date values that formulas can use.

Can I round age to the nearest half-year?

Yes. Use =ROUND(DATEDIF(A2,TODAY(),"M")/12,1) to show age in years rounded to one decimal place. This divides months by 12 to get years, then rounds to one digit after the decimal (0.5, 1.5, etc.).

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.