To calculate a person’s completed age in years in Excel, put the birthdate in cell A2 and enter =DATEDIF(A2,TODAY(),"y"). The formula counts full years between that date and today, so it does not add the next year until the birthday has passed.
Calculate completed age in years
Enter a valid Excel date of birth in A2, then put this formula in another cell:
As an Amazon Associate I earn from qualifying purchases.
=DATEDIF(A2,TODAY(),"y")DATEDIF calculates the interval between two dates; the unit "y" asks for complete years. TODAY() supplies the current date. This is the closest match to the ordinary question “How old is this person?” See Microsoft’s date-difference guidance and its age-calculation examples.
Free tools Windows power users keep installed
One-click scans. No signup required.
Format the result cell as General or Number if Excel displays the result as a date rather than a number; Microsoft’s age examples use those formats for numeric results.
Choose a formula for the result you need
| Goal | Formula | What it returns |
|---|---|---|
| Completed age in whole years | =DATEDIF(A2,TODAY(),"y") |
Full years elapsed; the best fit for conventional age in years. |
| Quick calendar-year difference | =YEAR(TODAY())-YEAR(A2) |
The difference between the current year and birth year. It may be one too high before the birthday that year. |
| Fractional years | =YEARFRAC(A2,TODAY(),1) |
An interval expressed as a fraction of a year, using the Actual/actual basis. |
The calendar-year subtraction is easy to read, but it does not check whether the birthday has occurred yet. Microsoft also shows a comparable formula using YEAR(NOW())-YEAR(A2) in its age guide. Use DATEDIF with "y" when the answer should be completed age, not merely the difference between two calendar years.
Make sure Excel recognizes the birthdate
Excel stores dates as serial numbers. For dependable date calculations, enter a date Excel recognizes as a date value; Microsoft recommends dates created with DATE or other date formulas and functions, and warns that text dates can cause problems. The date and time function reference provides more on Excel’s date functions.
- If the formula returns an error or an unexpected result, check that A2 contains a real date value rather than text that only looks like a date.
- If the calculated age appears in a date-like format, change the result cell’s number format to General or Number.
Why the age may not update today
TODAY() returns Excel’s current date and can change when the workbook recalculates. If the displayed age or date seems stale, check that calculation is set to Automatic. Microsoft documents the function and its recalculation behavior on the TODAY function page.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11When to use YEARFRAC instead
YEARFRAC returns a fractional-year interval, not completed age. In =YEARFRAC(A2,TODAY(),1), the final argument 1 selects Actual/actual. If you omit that argument, Excel uses basis 0, US (NASD) 30/360; Microsoft notes that this default basis can give an incorrect result when the start date is the last day of February. The function supports other bases as well: 2 is Actual/360, 3 is Actual/365, and 4 is European 30/360. Choose a basis to match the calculation you need, rather than treating a fractional year as interchangeable with completed age. Details are in Microsoft’s YEARFRAC documentation.
Rank #3
Calculate years, months, and days with care
For a breakdown, DATEDIF supports units for full years, remaining months after full years, and days. For example, a years-and-months display can combine DATEDIF(A2,TODAY(),"y") with DATEDIF(A2,TODAY(),"ym"). Microsoft warns that DATEDIF can produce incorrect results in some scenarios and specifically advises against relying on the "md" unit, which returns remaining days, because it may be inaccurate. Avoid presenting a years/months/days formula using "md" as universally reliable. See Microsoft’s explanation of date differences.
Excel compatibility
Microsoft lists the age-calculation guidance and relevant functions for Excel for Microsoft 365 and Excel 2024, 2021, 2019, and 2016. The TODAY and YEARFRAC function pages also list Mac editions for current supported versions.
Quick Recap
Best Value
- Used Book in Good Condition
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Recommended Free Tools
