The fastest way to calculate age in Excel

Excel can calculate someone's age in seconds using the DATEDIF function, which measures the time between a birth date and today's date. You enter the birth date in one cell, write a formula in another, and Excel does the math automatically — no manual subtraction needed.

The formula looks like this: =DATEDIF(A1,TODAY(),"Y"). The A1 is where you put the birth date, TODAY() tells Excel to use today's date, and the "Y" means you want the answer in years. If you change the birth date, the age updates when ready.

This method works in Excel on Windows, Mac, and in Excel Online. It also works in Google Sheets, though Google Sheets calls the function by a slightly different name in some versions.

Key Takeaways

  • The DATEDIF formula calculates age by measuring years between a birth date and today, and updates automatically each day.
  • Birth dates must be entered as actual dates (like 3/15/1985), not text, or the formula will return an error.
  • You can calculate age in years, months, days, or any combination by changing the third part of the formula.
  • If DATEDIF does not work on your version of Excel, the YEARFRAC formula provides an alternative that works everywhere.

Setting up your spreadsheet with birth dates

Start by opening a blank Excel spreadsheet and entering birth dates in a single column. Click on a cell — let's say A1 — and type a date. Excel recognizes dates in most common formats: 3/15/1985, March 15 1985, 15-Mar-85, or 2/28/2010 all work.

If Excel turns your date into a number like 44927, you entered it as text instead of a date. To fix this, delete what you typed, click the cell again, and type the date with slashes: 3/15/1985. Excel will recognize it as a date and display it normally.

You can enter as many birth dates as you need, one per row. If you have names in column A and birth dates in column B, that works too — just adjust the formula to match.

Writing the DATEDIF formula step by step

Click on the cell where you want the age to appear. If your birth dates are in column A, click on cell B1 (or B2, B3, and so on). Type this exactly: =DATEDIF(A1,TODAY(),"Y")

Then press Enter. Excel calculates the age and shows a number — that is the person's age in complete years. If the birth date was 3/15/1985 and today is 3/20/2024, Excel shows 39.

To copy this formula down to all your other rows, click on the cell with the formula, then drag the small square in the bottom-right corner of the cell down as far as you need. Excel automatically adjusts the cell reference for each row, so B2 uses A2, B3 uses A3, and so on.

Getting age in months or days instead of years

The "Y" in the formula means years. You can change it to get different units. Use "M" for months: =DATEDIF(A1,TODAY(),"M") shows how many complete months old someone is. Use "D" for days: =DATEDIF(A1,TODAY(),"D") shows the total number of days.

You can also combine them to show years and months together. Use "Y" and "M" with a comma and ampersand: =DATEDIF(A1,TODAY(),"Y")&" years, "&DATEDIF(A1,TODAY(),"M"). This shows something like "39 years, 3 months" in a single cell.

For most purposes, years alone is what you need. Months and days are useful if you are tracking infant ages, calculating exact tenure for a job, or working with contracts that depend on precise time.

What to do if DATEDIF does not work

Some older versions of Excel or certain regional settings do not recognize DATEDIF. If you type the formula and see #NAME? error, use YEARFRAC instead: =INT(YEARFRAC(A1,TODAY()))

YEARFRAC calculates the exact fraction of a year between two dates, and INT rounds it down to a whole number. The result is the same as DATEDIF — it shows age in complete years. This formula works on nearly every version of Excel.

If you are using Google Sheets and DATEDIF does not work, try the YEARFRAC method first. If that also fails, you can use a longer formula: =YEAR(TODAY())-YEAR(A1)-IF(OR(MONTH(TODAY()). This breaks down the calculation into parts that every spreadsheet understands.

Common mistakes and how to fix them

The most common error is entering a birth date as text instead of a date. If you see #VALUE! or #NUM! error, the formula cannot read the birth date. Delete the date, re-enter it with slashes (3/15/1985), and try again.

Another mistake is using the wrong cell reference. If your formula says =DATEDIF(B1,TODAY(),"Y") but your birth dates are in column A, change B1 to A1. The formula will not work if it is looking in the wrong column.

If the age looks wrong — too high or too low by a year — check whether the person has had their birthday this year. DATEDIF counts only complete years, so someone born on December 25, 1985 is still 38 years old on December 24, 2024, and turns 39 the next day.

Using age calculations for real situations

Age calculations in Excel are useful for tracking employee tenure, managing a contact list, identifying which customers may have access to for age-based discounts, or organizing a class roster. Once you set up the formula, it updates every day without you doing anything — someone's age advances automatically on their birthday.

If you need to freeze the age at a specific date instead of using today's date, replace TODAY() with a specific date. For example, =DATEDIF(A1,DATE(2024,12,31),"Y") calculates how old someone was on December 31, 2024. This is useful for historical records or reports that need to show ages as of a particular date.

Frequently Asked Questions

Why does Excel show my date as a number like 45000?

You typed the date as text instead of as a date. Delete it and re-enter it with slashes, like 3/15/1985. Excel will recognize it as a date and display it normally. If it still shows as a number, right-click the cell, choose Format Cells, and select Date from the Category list.

Can I calculate age as of a date other than today?

Yes. Replace TODAY() with a specific date using DATE(year,month,day). For example, =DATEDIF(A1,DATE(2025,6,15),"Y") calculates age as of June 15, 2025. This is useful for reports or historical records.

What if someone's birth date is incomplete, like I only know the year?

DATEDIF needs a complete date to work. If you only have a year, you can estimate by using January 1 of that year: 1/1/1985. The age will be off by up to one year, but it gives you a rough number. For precise ages, you need the full birth date.

Does the age update automatically every day?

Yes. Because the formula uses TODAY(), which changes every day, the age increases by one on each birthday automatically. You do not need to edit the formula or update the spreadsheet.

Can I use this formula in Google Sheets?

Yes. DATEDIF works in Google Sheets the same way it works in Excel. If it does not work, try YEARFRAC with INT: =INT(YEARFRAC(A1,TODAY())). Both formulas produce the same result.