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 cell, and Excel will show the current age.

DATEDIF is built into Excel specifically for this task. The "Y" at the end tells Excel you want the answer in years. You can also use "M" for months or "D" for days if you need a more precise measurement, though years is what most people need.

This formula updates automatically every day, so the age will increment on the person's birthday without you having to change anything.

Key Takeaways

  • Use =DATEDIF(A2, TODAY(), "Y") to calculate age in years from a birth date in cell A2.
  • DATEDIF automatically updates each day, so birthdays are handled without manual changes.
  • You can calculate age in months with "M" or days with "D" instead of "Y" if you need finer detail.
  • If DATEDIF does not work, your birth date may be formatted as text instead of a date — convert it first by using the DATEVALUE function.

Setting up your spreadsheet for age calculation

Put all birth dates in a single column so you can copy the formula down and calculate multiple ages at once. Column A works well for this. Make sure each cell contains only the date — no extra text like "Born: 1985-03-15" — because Excel needs to read it as a date value, not text.

In the column next to your birth dates (usually column B), click the first empty cell and type your DATEDIF formula. Then click and drag the small square at the bottom right corner of that cell down to fill the formula into all the rows below. Excel will automatically adjust the cell reference (A2 becomes A3, A4, and so on) as you drag.

If you have headers, put "Birth Date" in A1 and "Age" in B1, then start your formula in B2. This keeps your spreadsheet organized and makes it clear what each column contains.

What to do if DATEDIF gives you an error

The most common error is #VALUE!, which means Excel cannot read your birth date as a date. This happens when the date is stored as text instead of as a date value. To fix this, use =DATEDIF(DATEVALUE(A2), TODAY(), "Y"). The DATEVALUE function converts text that looks like a date into an actual date that Excel can calculate with.

If you still see an error, check that your birth date is in a standard format like MM/DD/YYYY or YYYY-MM-DD. Unusual formats or dates with extra characters will cause problems. Delete the cell and retype the date in a standard format, then try the formula again.

Another less common error is #NUM!, which means the birth date is in the future or the formula is backwards. Make sure your formula reads =DATEDIF(birth_date, TODAY(), "Y") with the birth date first and TODAY() second.

Using YEARFRAC if you need age with decimals

If you want to show age as a decimal (for example, 25.5 years instead of just 25), use =YEARFRAC(A2, TODAY()). This calculates the exact fraction of a year that has passed since the birth date. It is useful when you need precision for medical records, insurance calculations, or research data.

YEARFRAC does not require a third parameter like DATEDIF does. It automatically returns the result as a decimal number. If you want to round it to one decimal place, wrap it in the ROUND function: =ROUND(YEARFRAC(A2, TODAY()), 1).

Calculating age as of a specific date instead of today

If you need to calculate how old someone was on a past date — for example, their age on January 1, 2020 — replace TODAY() with that date. The formula becomes =DATEDIF(A2, DATE(2020, 1, 1), "Y"). The DATE function lets you specify any year, month, and day in parentheses as (year, month, day).

This is useful when you are working with historical data or need to know ages as they were at a specific point in time. You can also put the target date in a cell (say, C1) and reference it in your formula: =DATEDIF(A2, C1, "Y"). Then you can change the date in C1 and all ages will recalculate instantly.

Handling leap years and edge cases

DATEDIF counts complete years, so someone born on March 15, 1990 will show as age 34 until March 15, 2024, when they turn 35. The formula does not round up or estimate — it counts only finished years. This is the standard way age is calculated in most contexts.

Leap years are handled automatically by Excel's date system, so you do not need to do anything special. If someone was born on February 29, Excel treats that date like any other, and the age calculation works correctly.

If your spreadsheet includes people with unknown birth dates, leave those cells blank. The DATEDIF formula will show an error for those rows, which makes it obvious that data is missing. You can also use an IF statement to hide the error: =IF(A2="", "", DATEDIF(A2, TODAY(), "Y")) will show nothing if the cell is empty, and the age if it contains a date.

Frequently Asked Questions

Can I calculate age without using DATEDIF?

Yes. You can use =INT((TODAY()-A2)/365.25), which divides the number of days between the birth date and today by 365.25 (accounting for leap years). This is less precise than DATEDIF because it does not account for the exact month and day, but it works in older versions of Excel where DATEDIF may not be available.

Why does my age formula show a negative number?

The birth date is probably in the future, or the formula is backwards. Check that your birth date is actually in the past and that the formula reads =DATEDIF(birth_date, TODAY(), "Y") with the birth date first. If the birth date is correct and in the past, the formula should return a positive number.

How do I format the age column to show no decimal places?

Right-click the column, select Format Cells, choose Number, and set Decimal Places to 0. Or select the column and use the decrease decimal button in the toolbar. DATEDIF already returns whole numbers, so this is mainly useful if you are using YEARFRAC and want to clean up the display.

Can I calculate age in months or days instead of years?

Yes. Use =DATEDIF(A2, TODAY(), "M") for months or =DATEDIF(A2, TODAY(), "D") for days. You can also combine them: =DATEDIF(A2, TODAY(), "Y")&" years, "&DATEDIF(A2, TODAY(), "M")&" months" will show age as "25 years, 3 months".

What if the birth date is in a different column or sheet?

Reference it by sheet name and cell. If the birth date is in Sheet2, cell A2, use =DATEDIF(Sheet2!A2, TODAY(), "Y"). The exclamation mark tells Excel to look in a different sheet. This works the same way whether the other sheet is in the same workbook or a linked file.