To calculate age from a birthdate in Excel, put the birthdate in cell A2 and type =DATEDIF(A2,TODAY(),"Y") in another cell. Excel shows the person’s age in whole years, and it updates every day. For an age with a decimal, use =YEARFRAC(A2,TODAY()).
This guide uses Microsoft’s DATEDIF function, Calculate age and Calculate the difference between two dates articles.
In this article:
- Calculate age in years
- How the DATEDIF formula works
- Show age in years, months and days
- Other age formulas
- Fix strange numbers and errors
- Frequently asked questions
How to Calculate Age in Years in Excel
- Type the birthdate in a cell, such as A2. Use a date format Excel recognizes, like 3/15/1990.
- Click the cell where you want the age.
- Type
=DATEDIF(A2,TODAY(),"Y")and press Enter. - To do a whole list, drag the fill handle down the column.
Result: the cell shows the person’s age in complete years, and it changes automatically on their birthday.
How the DATEDIF Formula Works
DATEDIF has three parts: DATEDIF(start_date,end_date,unit). Microsoft notes the function is useful in formulas where you need to calculate an age.
- start_date is the birthdate (A2).
- end_date is the date to measure to.
TODAY()uses today’s date. - unit tells Excel what to count. “Y” counts complete years, “M” complete months and “D” days. “YM” counts the months left over after the last full year, and “YD” counts days while ignoring the years.
Two cautions from Microsoft: Excel includes DATEDIF to support older workbooks from Lotus 1-2-3, and it may calculate incorrect results in some situations. Microsoft specifically doesn’t recommend the “MD” unit, which can return a negative number, zero or an inaccurate result.
Result: you know which unit to use for the age you need.
How to Show Age in Years, Months and Days
Microsoft’s method builds the result in three pieces. With the birthdate in A2:
- Years:
=DATEDIF(A2,TODAY(),"Y") - Remaining months:
=DATEDIF(A2,TODAY(),"YM") - Days: instead of “MD”, Microsoft subtracts the first day of the ending month from the end date. With today as the end date, that’s
=TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),1).
To show years and months in one cell, join them with text: =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months".
Result: you get an age like “35 years, 6 months” instead of just “35”.
Other Age Formulas in Excel
- Age with decimals:
=YEARFRAC(A2,TODAY()). Microsoft’s Calculate age article uses YEARFRAC for a year-fractional age. - Age on a certain date: replace TODAY() with a cell that holds the date, such as
=DATEDIF(A2,B2,"Y"). - Quick year subtraction: Microsoft also shows
=(YEAR(NOW())-YEAR(A2)). It’s simple, but it only subtracts the years, so it can be one year too high if the person’s birthday hasn’t happened yet this year. That’s why DATEDIF is the better choice for exact ages. - Age in days:
=DAYS(TODAY(),A2)returns the number of days between the birthdate and today.
Result: you can pick the age format that fits your sheet.
How to Fix Strange Numbers and Errors
- You see a date instead of an age: Microsoft says to make sure the cell is formatted as a number or General.
- You see #NUM!: Microsoft says DATEDIF returns #NUM! when the start date is later than the end date. Check that the birthdate is in the first argument.
- You see #VALUE!: Excel probably didn’t recognize the birthdate as a date. Retype it in a date format Excel accepts.
- Dates look wrong: if your dates appear in an unexpected order, check your system date settings. See how to change the date format in Windows 11.
Want to list people from oldest to youngest? See how to sort by date in Excel Online; the same date rules apply.
Result: your age column shows clean numbers.
Frequently Asked Questions
Does the age update automatically?
Yes. Formulas that use TODAY() recalculate based on the current date.
Why does Microsoft warn about DATEDIF?
Microsoft says DATEDIF exists to support older Lotus 1-2-3 workbooks and may give incorrect results in some cases, especially with the “MD” unit. The “Y” and “YM” units used in this guide are the ones Microsoft uses in its own age examples.
How do I calculate age in months?
Use =DATEDIF(A2,TODAY(),"M") for complete months.
Can I calculate the age for a whole column at once?
Yes. Enter the formula once, then drag the fill handle down.
For more Excel help, see our Excel guides. Related: how to calculate percentage in Excel and why Excel changes numbers to dates.
Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read hundreds of millions of times.
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.
His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.