How to Calculate Age in Excel Using Formulas and Functions

The fastest way to calculate age in Excel

The most reliable method is the DATEDIF function, which calculates the difference between two dates in years, months, or days. In a cell, type =DATEDIF(birth_date, TODAY(), "Y") where birth_date is the cell containing someone's date of birth. This returns their age in complete years.

If DATEDIF does not work in your version of Excel, use the YEARFRAC function wrapped in INT: =INT(YEARFRAC(birth_date, TODAY())). Both formulas update automatically each day, so the age increases on the person's birthday without you having to edit the spreadsheet.

A third option that works everywhere is the YEAR function combined with MONTH and DAY: =YEAR(TODAY())-YEAR(birth_date)-IF(OR(MONTH(birth_date)>MONTH(TODAY()),AND(MONTH(birth_date)=MONTH(TODAY()),DAY(birth_date)>DAY(TODAY()))),1,0). This is longer but handles the edge case where the birthday has not yet occurred this year.

Key Takeaways

DATEDIF is the simplest formula: =DATEDIF(birth_date, TODAY(), "Y") returns age in years and updates automatically.
If DATEDIF does not work, use =INT(YEARFRAC(birth_date, TODAY())) instead — both produce the same result.
The YEAR-MONTH-DAY formula is longer but works in all Excel versions and correctly handles birthdays that have not yet occurred this year.
Always use TODAY() or a current date cell as the second date so ages update without manual editing.

Setting up your spreadsheet for age calculations

Start by putting birth dates in one column — usually column B — in a standard date format like 1/15/1985 or 01-15-1985. Excel recognizes both. If your dates are stored as text (they look like dates but will not sort correctly), convert them first by using Data > Text to Columns, selecting Delimited, and clicking Finish.

In the column where you want ages to appear — often column C — click the first empty cell and type your formula. For example, if birth dates are in B2 through B100, type =DATEDIF(B2,TODAY(),"Y") in cell C2. Then click that cell and drag the small square at the bottom-right corner down to C100 to copy the formula to all rows.

Excel will automatically adjust the cell reference for each row, so B2 becomes B3, B4, and so on. The TODAY() function stays the same in every row because it is a fixed function, not a cell reference.

Using DATEDIF for months and days instead of years

DATEDIF can return age in different units by changing the third argument. Use "Y" for years, "M" for months since the last birthday, or "D" for days since the last birthday.

If you need the total number of months someone has lived (not just months since their last birthday), use =DATEDIF(birth_date, TODAY(), "M"). For total days, use "D". These are useful for medical records, insurance calculations, or tracking infant ages in months.

You can also combine them in one cell to show age as "25 years, 3 months, 12 days" by using three separate DATEDIF formulas in the same cell: =DATEDIF(B2,TODAY(),"Y")&" years, "&DATEDIF(B2,TODAY(),"YM")&" months, "&DATEDIF(B2,TODAY(),"MD")&" days". The & symbol joins text and numbers together.

Troubleshooting common age calculation errors

If your formula returns #NUM! error, the birth date is likely after the current date, or the date format is not recognized. Check that the birth date cell actually contains a date — if it looks like a date but is stored as text, Excel cannot do math with it. Right-click the cell, select Format Cells, and change the format to Date.

If ages are off by one year, check whether the person's birthday has already occurred this year. DATEDIF counts complete years, so someone born on December 25, 1985 is still 38 years old on December 24, 2024, and turns 39 on December 25, 2024. If you need to round up instead, use =CEILING(YEARFRAC(birth_date, TODAY()),1).

If the formula does not update when you open the file the next day, Excel may have automatic calculation turned off. Go to Formulas > Calculation Options and select Automatic. If you are sharing the file with others, note that TODAY() uses the computer's system date, so ages may differ by a day if someone opens it in a different time zone.

Calculating age from a specific date instead of today

Instead of TODAY(), you can use any date you want as the reference point. This is useful if you are analyzing historical data or need to know what someone's age was on a specific date in the past.

Replace TODAY() with a date in quotes, like =DATEDIF(B2,"12/31/2020","Y") to find out how old someone was on December 31, 2020. Or reference a cell containing a date, like =DATEDIF(B2,D2,"Y") if column D holds the reference date you want to use.

This approach is common in payroll, where you need to know an employee's age as of their hire date or as of a specific benefits calculation date, rather than their current age.

Comparing DATEDIF, YEARFRAC, and the YEAR formula

DATEDIF is the fastest to type and the easiest to read, but it is not available in all versions of Excel — notably, some older versions and Google Sheets do not support it. YEARFRAC works in nearly every version and produces the same result when wrapped in INT, but it is slightly slower to calculate on very large spreadsheets with thousands of rows.

The YEAR-MONTH-DAY formula works everywhere and is the most transparent about what it is doing (subtracting birth year from current year, then adjusting if the birthday has not occurred yet). It is longer to type and harder to read, but it never fails.

For most users, start with DATEDIF. If it does not work, switch to YEARFRAC. Use the YEAR formula only if you need maximum compatibility or are working in a system that does not support the other two.

Frequently Asked Questions

What if my birth date is in a different format, like "January 15, 1985"?

Excel recognizes most common date formats automatically. If your formula returns an error, right-click the birth date cell, select Format Cells, choose the Date category, and pick a standard format like MM/DD/YYYY. Then re-enter the date or copy and paste it back into the cell so Excel re-reads it as a date, not text.

Can I calculate age in Excel on a Mac?

Yes. DATEDIF, YEARFRAC, and the YEAR formula all work the same way in Excel for Mac. The only difference is that Mac Excel may use a different date system internally, but the formulas handle this automatically and produce the correct age.

How do I calculate someone's age if I only know their birth year, not the full date?

Use =YEAR(TODAY())-birth_year to subtract the birth year from the current year. This gives an approximate age but may be off by one year depending on whether their birthday has occurred. For exact age, you need the full birth date including month and day.

What happens to the age formula if I copy the spreadsheet to a different computer?

The formula continues to work and updates based on that computer's system date. If you open the file on January 1 on one computer and January 2 on another, the ages will differ by one day because TODAY() uses the local system date. To avoid this, replace TODAY() with a fixed date in a cell that you update manually.

Can I use age calculations in conditional formatting or other Excel features?

Yes. You can use an age formula in conditional formatting to highlight rows where age is above or below a threshold, or in pivot tables to group people by age ranges. The formula works anywhere you would normally put a calculation — in sorting, filtering, charts, and data validation rules.

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.