Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To calculate a person’s completed age in years in Excel, put their birthdate in cell A2 and enter =DATEDIF(A2,TODAY(),"y"). The formula counts full years between the birthdate and today, so it does not add a year before the person’s birthday.
Calculate age in completed years
- Enter the birthdate in A2 as an Excel date value.
- In another cell, enter
=DATEDIF(A2,TODAY(),"y"). - Press Enter. The result is the number of full years elapsed as of the current date.
DATEDIF uses a start date, an end date, and a unit. Here, A2 is the birthdate, TODAY() supplies the end date, and "y" requests completed years. Microsoft documents DATEDIF for calculating the difference between dates and gives age calculation as a use case: Calculate the difference between two dates.
Choose the formula for the result you need
| Goal | Formula | What to know |
|---|---|---|
| Completed age in whole years | =DATEDIF(A2,TODAY(),"y") |
Counts full years; appropriate for a conventional age in years. |
| Approximate calendar-year difference | =YEAR(TODAY())-YEAR(A2) |
Subtracts the birth year from the current year, but can be one year too high before the birthday. |
| Fractional years elapsed | =YEARFRAC(A2,TODAY(),1) |
Returns a fraction of a year using the Actual/actual basis; it is not the same measure as completed age. |
Microsoft’s age guide also shows an approximate day-count approach using 365.25 days per year to account for leap years. That is a calculation convention for an approximate result, not a substitute for counting completed birthdays: Calculate age.
Free tools Windows power users keep installed
One-click scans. No signup required.
Use a real date value in the birthdate cell
Excel stores dates as serial numbers. Use a date entered in a format Excel recognizes, or create it with DATE or another date function. A date stored as text may not behave as expected in calculations. Microsoft’s date and time function reference describes Excel’s date functions and date handling.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
If the formula result appears as a date or otherwise has an unexpected display, change the result cell’s number format to General or Number.
Why TODAY() may not appear to update
TODAY() returns Excel’s current date and updates when the workbook recalculates. If the age stays unchanged after the date has advanced, check that calculation is set to Automatic. Microsoft documents the function and its recalculation behavior on the TODAY function page.
When to use YEARFRAC instead
Use YEARFRAC when you specifically need a fractional-year interval, such as a decimal value for elapsed time. Its optional third argument selects a day-count basis. In =YEARFRAC(A2,TODAY(),1), basis 1 means Actual/actual. If omitted or set to 0, Excel uses US (NASD) 30/360; Microsoft notes that this default basis can return an incorrect result when the start date is the last day of February. Other documented bases are 2 (Actual/360), 3 (Actual/365), and 4 (European 30/360). See Microsoft’s YEARFRAC function documentation.
Age in years, months, and days
DATEDIF can also return years, remaining months, and days through separate calculations. The "ym" unit gives remaining months after full years. Microsoft cautions that DATEDIF can produce incorrect results in some scenarios and specifically advises against relying on the "md" unit because it may be inaccurate. For that reason, the years-only formula is the safer straightforward choice when the needed answer is completed age.
Microsoft lists support for the age calculation and relevant functions in Excel for Microsoft 365, Excel 2024, 2021, 2019, and 2016. The TODAY and YEARFRAC documentation also lists supported Mac editions.
Quick Recap
Rank #4
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.
