How to Calculate Age in Excel Using Formulas and Functions

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 two dates. The formula is =DATEDIF(birth_date, TODAY(), "Y"), where "Y" means you want the answer in years. This works in most versions of Excel on Windows and Mac, though Google Sheets uses a slightly different syntax.

If DATEDIF does not work in your version, you can use =INT((TODAY()-birth_date)/365.25) instead. This divides the number of days between today and the birth date by 365.25 (accounting for leap years) and rounds down to a whole number. Both methods give you the person's age in years as of today's date.

Key Takeaways

DATEDIF is the built-in Excel function designed for age calculation and works across most versions, though it is not available in all spreadsheet programs.
The formula =DATEDIF(birth_date, TODAY(), "Y") calculates age in complete years, ignoring months and days.
If DATEDIF is not available, =INT((TODAY()-birth_date)/365.25) produces the same result by dividing total days by the average year length.
You can calculate age in months or days by changing the third argument in DATEDIF from "Y" to "M" or "D".
Both formulas update automatically each day, so a spreadsheet with these formulas will always show current ages without manual updates.

Setting up your data before you write the formula

Before you enter a formula, make sure your birth dates are in a column that Excel recognizes as dates, not text. If you paste dates from another source, Excel sometimes treats them as plain text, and the formula will return an error. To check, click on a cell with a date and look at the formula bar — if it shows the date with an apostrophe at the start (like '1/15/1985), it is text and needs to be converted.

To convert text dates to real dates, select the column, go to the Data menu, choose Text to Columns, and click Finish. This forces Excel to reread the column as dates. Once your dates are formatted correctly, you can write your age formula in a new column next to them.

Using DATEDIF for age in years, months, or days

The DATEDIF function lets you measure the gap between two dates in different units. The basic structure is =DATEDIF(start_date, end_date, unit). For age, your start date is the birth date and your end date is TODAY() (which is always today's date). The unit is what changes depending on what you want to know.

Use "Y" for complete years: =DATEDIF(A2, TODAY(), "Y") returns 34 if the person is 34 years old. Use "M" for complete months: =DATEDIF(A2, TODAY(), "M") returns the total number of months lived (so 408 for a 34-year-old). Use "D" for days: =DATEDIF(A2, TODAY(), "D") returns the total number of days since birth. You can also combine units — =DATEDIF(A2, TODAY(), "Y")&" years, "&DATEDIF(A2, TODAY(), "YM")&" months" — to show age as "34 years, 7 months".

The alternative formula when DATEDIF is not available

Google Sheets and some older versions of Excel do not have DATEDIF. In those cases, use =INT((TODAY()-birth_date)/365.25). This subtracts the birth date from today's date, which gives you the number of days lived. Dividing by 365.25 converts days to years (the .25 accounts for leap years), and INT rounds down to a whole number.

This formula is less precise than DATEDIF because it does not account for the exact calendar — it assumes every year is 365.25 days. For most purposes this is close enough, but if you need age in months or days, stick with DATEDIF or use a more complex formula. In Google Sheets, the equivalent of DATEDIF is =DATEDIF(A2, TODAY(), "Y"), which works the same way as Excel.

Handling dates that have not happened yet or are invalid

If someone enters a birth date in the future or a date that does not exist (like February 30), your formula will return an error. To prevent this, wrap your formula in an IFERROR function: =IFERROR(DATEDIF(A2, TODAY(), "Y"), "Invalid date"). This tells Excel to show "Invalid date" instead of an error if something goes wrong.

You can also add a check to make sure the birth date is not in the future: =IF(A2>TODAY(), "Birth date is in the future", DATEDIF(A2, TODAY(), "Y")). This is useful if you are building a spreadsheet that other people will use and you want to catch data entry mistakes before they cause problems.

Copying the formula down to multiple rows

Once you have written the formula in one cell, you can copy it down to calculate age for everyone in your list. Click the cell with your formula, then drag the small square in the bottom right corner down to the last row with data. Excel will automatically adjust the cell reference for each row — so if your first formula is =DATEDIF(A2, TODAY(), "Y"), the second row will become =DATEDIF(A3, TODAY(), "Y"), and so on.

Alternatively, click the cell with your formula, copy it (Ctrl+C or Cmd+C), select the range where you want to paste it, and paste (Ctrl+V or Cmd+V). Both methods work the same way. The formula will update automatically every day, so if you open the spreadsheet tomorrow, all the ages will be one day older.

When to use a fixed date instead of TODAY()

Sometimes you do not want age to change every day. If you are recording someone's age as of a specific date — like their age on the day they enrolled in a program or the day they took a test — use a fixed date instead of TODAY(). Replace TODAY() with a date in quotes: =DATEDIF(A2, "12/31/2023", "Y") calculates age as of December 31, 2023, and never changes.

This is useful for historical records or when you need to compare ages across a group on the same reference date. You can also put the reference date in its own cell and reference that cell in your formula: =DATEDIF(A2, $E$1, "Y"). The dollar signs lock the reference to cell E1, so when you copy the formula down, it always uses the same date while the birth date changes for each row.

Frequently Asked Questions

Why does DATEDIF give me an error?

DATEDIF is not available in all versions of Excel or in Google Sheets. If you see a #NAME? error, your spreadsheet program does not recognize the function. Use the alternative formula =INT((TODAY()-birth_date)/365.25) instead, or check your program's documentation to see if DATEDIF is supported.

How do I show age in years and months together?

Use =DATEDIF(A2, TODAY(), "Y")&" years, "&DATEDIF(A2, TODAY(), "YM")&" months". The "YM" unit in the second DATEDIF shows only the months beyond complete years, so someone who is 34 years and 7 months old will display as "34 years, 7 months".

What if the birth date is in a different format?

Excel usually recognizes common date formats automatically, but if your dates are in an unusual format (like "15-Jan-1985" or "1985/01/15"), make sure the column is formatted as a date. Right-click the column, select Format Cells, choose Date, and pick a format. Then your formula should work.

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

Yes. Replace TODAY() with any date in quotes: =DATEDIF(A2, "6/15/2024", "Y") calculates age as of June 15, 2024. You can also put the date in a cell and reference it with dollar signs to lock it: =DATEDIF(A2, $E$1, "Y").

Does the formula account for leap years?

DATEDIF accounts for leap years automatically because it counts actual calendar days. The alternative formula =INT((TODAY()-birth_date)/365.25) approximates leap years by dividing by 365.25, which is close but not exact for very precise calculations.

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.