How to Calculate Age in Excel Using a Simple Formula

The fastest way to calculate age in Excel

To calculate someone's age in Excel, use the DATEDIF function, which measures the time between two dates. The formula is =DATEDIF(birth_date, TODAY(), "Y"), where birth_date is the cell containing the person's date of birth and "Y" tells Excel to count complete years. This gives you their age as of today.

If you want age as of a specific date instead of today, replace TODAY() with that date. For example, =DATEDIF(A2, DATE(2024,12,31), "Y") calculates how old someone was on December 31, 2024. The formula works in all modern versions of Excel on Windows and Mac.

DATEDIF is the standard choice because it counts only complete years — a person born on March 15, 2000 will show as 24 years old until March 15, 2025. Other methods exist but require more steps or produce decimals you then have to round.

Key Takeaways

The DATEDIF formula =DATEDIF(birth_date, TODAY(), "Y") calculates age in complete years and works in all current Excel versions.
Birth dates must be formatted as actual dates, not text — if Excel treats the date as text, the formula returns an error.
You can calculate age as of any date by replacing TODAY() with a specific date in the formula.
DATEDIF counts only complete years, so someone born March 15, 2000 remains 24 years old until March 15, 2025.

Setting up your spreadsheet with birth dates

Before you write the age formula, make sure your birth dates are in a format Excel recognizes. Put each birth date in its own cell — for example, column A. Excel accepts dates typed as 3/15/2000, 03-15-2000, March 15 2000, or 15-Mar-2000, depending on your region's date format. The exact format does not matter as long as Excel converts it to a date.

To check whether Excel sees the date correctly, click the cell and look at the formula bar at the top. If it shows a date (not the text you typed), Excel recognizes it. If it shows exactly what you typed as text, right-click the cell, choose Format Cells, and change the format to Date. Then re-enter the birth date.

Once your birth dates are in place, move to the column where you want ages to appear — usually column B if birth dates are in column A. Click the first empty cell in that column and type the DATEDIF formula.

Writing and copying the DATEDIF formula

In the cell where you want the age to appear, type =DATEDIF(A2,TODAY(),"Y") (assuming the birth date is in cell A2). Press Enter. Excel calculates the age and shows it as a whole number.

To explore this formula to multiple rows, click the cell with the formula you just created. You will see a small square in the bottom-right corner of the cell — this is the fill handle. Click and drag it down to copy the formula to as many rows as you need. Excel automatically adjusts the cell reference for each row: A2 becomes A3, A4, A5, and so on.

If dragging feels awkward, you can also copy the cell (Ctrl+C or Cmd+C), select the range where you want it pasted, and paste (Ctrl+V or Cmd+V). Excel will adjust the references automatically.

Calculating age in years and months

If you need more detail than just years — for example, someone's age as "24 years, 7 months" — use DATEDIF with multiple parts. Create two formulas side by side: =DATEDIF(A2,TODAY(),"Y") for years and =DATEDIF(A2,TODAY(),"YM") for the remaining months after the last birthday.

The "YM" parameter tells DATEDIF to count only the months that do not add up to a full year. So if someone is 24 years and 7 months old, the first formula shows 24 and the second shows 7. You can combine them into a single cell with =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months", which produces text like "24 years, 7 months".

This approach works well for records where you need precision — employment records, medical charts, or age verification. For straightforward age counts, the single "Y" formula is usually enough.

What to do when DATEDIF returns an error

If your formula shows #NUM! or #VALUE!, the most common cause is that Excel does not recognize one of the dates as a date. Check that the birth date cell is formatted as a date, not text. Click the cell with the birth date, right-click, choose Format Cells, and select Date from the Category list on the left.

Another cause is a birth date that is later than the comparison date — for example, a birth date of 2025 when you are calculating age as of today in 2024. DATEDIF cannot calculate backwards. If this is intentional (you are working with future dates), you will need a different approach.

If the formula still does not work after checking the date format, try re-entering the birth date from scratch. Sometimes dates pasted from other sources carry hidden formatting that Excel cannot parse. Type it directly into the cell instead.

Alternative methods if DATEDIF does not work

In rare cases — usually in older Excel versions or specific regional settings — DATEDIF may not be available. A backup formula is =INT((TODAY()-A2)/365.25), which divides the number of days between the birth date and today by the average days in a year (accounting for leap years). This produces age in years, though it is slightly less precise than DATEDIF because it does not account for the exact date of birth.

Another option is =YEAR(TODAY())-YEAR(A2), which subtracts the birth year from the current year. This method is simpler but gives inaccurate results for people who have not yet had their birthday this year — it will show them one year older than they actually are until their birthday passes.

For most situations, DATEDIF is the clearest and most reliable choice. Use the alternatives only if DATEDIF produces an error and you cannot fix the date format.

Frequently Asked Questions

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

Yes. Replace TODAY() with a specific date using the DATE function. For example, =DATEDIF(A2,DATE(2024,12,31),"Y") calculates age as of December 31, 2024. The DATE function takes the year, month, and day in that order, separated by commas.

Why does my formula show someone one year older than they should be?

This usually means you used the YEAR subtraction method instead of DATEDIF. The formula =YEAR(TODAY())-YEAR(A2) does not account for whether the person has had their birthday yet this year. Switch to DATEDIF to fix it.

What if the birth date is in a different column for each row?

The formula still works the same way. If birth dates are in column C instead of column A, use =DATEDIF(C2,TODAY(),"Y") and copy it down. Excel adjusts the column letter automatically as you copy.

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

Yes. Replace "Y" with "D" for days or use =INT(DATEDIF(A2,TODAY(),"D")/7) for weeks. DATEDIF supports "D" (days), "M" (months), and "Y" (years) as the main units.

Does DATEDIF work on Excel for Mac?

Yes, DATEDIF works on Excel for Mac in all recent versions. The formula and syntax are identical to Windows Excel.

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.