The fastest way to calculate age in Excel

The simplest formula to calculate age from a birth date is =DATEDIF(birth_date, TODAY(), "Y"). This formula subtracts the birth date from today's date and returns the result in complete years. If your birth date is in cell A2, you would type =DATEDIF(A2, TODAY(), "Y") into any empty cell, and Excel will show the current age.

DATEDIF is built into Excel and works in both Windows and Mac versions. The formula updates automatically every day, so the age will always be current without you having to change anything. This is the method most people use because it handles leap years and month lengths correctly without extra work.

If DATEDIF does not work in your version of Excel, a backup formula is =INT((TODAY()-A2)/365.25). This divides the number of days between the birth date and today by the average days in a year. It is slightly less precise for people very close to a birthday, but it works everywhere.

Key Takeaways

  • The DATEDIF formula =DATEDIF(A2, TODAY(), "Y") calculates age in complete years and updates automatically each day.
  • The birth date must be formatted as a date in Excel, not as text, or the formula will return an error.
  • You can calculate age in months or days by changing the third part of DATEDIF from "Y" to "M" or "D".
  • If DATEDIF does not work, the formula =INT((TODAY()-A2)/365.25) gives nearly identical results for most purposes.

Setting up your spreadsheet with birth dates

Before you write any formula, make sure your birth dates are actually stored as dates, not as text. If you paste birth dates from another source, Excel sometimes treats them as text strings, and your formula will fail. The easiest way to check is to click on a cell with a birth date and look at the formula bar at the top — if you see an apostrophe before the date, it is text.

To convert text dates to real dates, select all the cells with birth dates, then go to the Data tab and click Text to Columns. Click Next twice, make sure the column format is set to Date, and click Finish. Excel will convert them all at once. After that, your DATEDIF formula will work.

If your birth dates are already correct, just click an empty cell next to the first birth date and type your formula. You can then copy the formula down to all the other rows by clicking the cell, copying it, selecting the range below, and pasting.

Understanding the DATEDIF formula parts

DATEDIF takes three pieces of information in order: the start date, the end date, and the unit you want to measure. In =DATEDIF(A2, TODAY(), "Y"), the start date is A2 (the birth date), the end date is TODAY() (which Excel fills in automatically), and "Y" means you want the answer in years.

You can change the third part to get different results. Use "M" to get the number of complete months between the dates, or "D" to get the number of days. For example, =DATEDIF(A2, TODAY(), "M") tells you how many months old someone is. This is useful if you are tracking ages for children under one year or need more precision than years alone.

The TODAY() function always returns the current date, so your age calculation updates on its own. If you want to calculate age as of a specific date instead of today, you can replace TODAY() with that date. For instance, =DATEDIF(A2, DATE(2025, 12, 31), "Y") would calculate how old someone was on December 31, 2025.

Calculating age in years and months together

Sometimes you need to show age as "25 years, 3 months" instead of just "25 years". You can do this by combining two DATEDIF formulas. Use =DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months". The "YM" unit in the second formula gives you only the remaining months after the complete years are counted.

This formula creates text that combines the years and months. If someone is 25 years and 3 months old, the cell will display exactly that. The & symbol joins pieces of text together in Excel, and the words in quotes ("years, " and "months") appear exactly as you type them.

You can adjust the text to match what you need. If you want just the numbers without the words, use =DATEDIF(A2, TODAY(), "Y") & "." & DATEDIF(A2, TODAY(), "YM") to show "25.3" instead. The format is up to you.

Fixing common errors with age formulas

The most common error is #NAME?, which means Excel does not recognize DATEDIF. This happens in older versions of Excel or when you misspell the function name. Check that you typed it exactly as shown, with no spaces. If it still does not work, use the backup formula =INT((TODAY()-A2)/365.25) instead.

The error #VALUE! usually means your birth date is stored as text, not as a date. Go back to the Data tab, use Text to Columns to convert it, and try the formula again. You can also try typing the birth date directly into the formula using DATE format: =DATEDIF(DATE(1990, 5, 15), TODAY(), "Y") would calculate the age of someone born May 15, 1990.

If your formula returns a negative number or zero when you know the person is older, check that your birth date is in the first part of the formula and today's date (or your target date) is in the second part. DATEDIF subtracts the first date from the second, so reversing them gives a negative result.

Using age calculations in larger spreadsheets

Once you have your age formula working in one cell, you can copy it down to calculate age for many people at once. Click the cell with your formula, copy it, then select all the cells below where you want the formula to appear. Paste, and Excel will automatically adjust the cell references for each row. If your birth dates are in column A, the formula will change from A2 to A3, A4, and so on.

You can also use age calculations in other formulas. For example, =COUNTIF(B:B, ">18") counts how many people in column B are older than 18, assuming column B contains ages you calculated with DATEDIF. Or use =AVERAGEIF(B:B, ">=65", C:C) to find the average value in column C for everyone 65 or older.

If you are working with a large dataset, calculate age once and leave it as a number rather than a formula that updates daily. This prevents the spreadsheet from recalculating every time you open it. Copy your age column, then use Paste Special (Ctrl+Shift+V on Windows, Cmd+Shift+V on Mac) and choose Values Only to replace the formulas with static numbers.

Frequently Asked Questions

Why does my DATEDIF formula show an error?

DATEDIF does not work if your birth date is stored as text instead of a date. Click on the cell with the birth date and look at the formula bar — if you see an apostrophe before the date, it is text. Use Data > Text to Columns to convert it to a real date, then try your formula again. If DATEDIF still does not work, your version of Excel may not support it; use =INT((TODAY()-A2)/365.25) instead.

Can I calculate age as of a date in the past instead of today?

Yes. Replace TODAY() with the date you want. For example, =DATEDIF(A2, DATE(2020, 1, 1), "Y") calculates how old someone was on January 1, 2020. You can also reference another cell: =DATEDIF(A2, B2, "Y") calculates the age between the date in A2 and the date in B2.

How do I show age in months for babies under one year?

Use =DATEDIF(A2, TODAY(), "M") to show age in complete months. For more detail, use =DATEDIF(A2, TODAY(), "M") & " months, " & DATEDIF(A2, TODAY(), "MD") & " days". The "MD" unit shows only the remaining days after complete months are counted.

Will my age formula update automatically?

Yes, because it uses TODAY(), which changes every day. The age will increase by one year on each birthday without you doing anything. If you want age to stay the same, replace TODAY() with a specific date, or convert the formula results to static numbers using Paste Special > Values.

What is the difference between DATEDIF and the 365.25 formula?

DATEDIF counts actual calendar days and accounts for leap years precisely. The 365.25 formula divides total days by the average year length, which is slightly less accurate but works in all Excel versions. For most purposes they give the same result, but DATEDIF is more reliable for people very close to a birthday.