How to Calculate Age in Excel: 3 Easy Formulas (DATEDIF, YEARFRAC, INT)
⚡ Quick Formula Cheat Sheet (TL;DR)
If your birth date is in cell B2, here are the three formulas you can copy directly into cell C2:
=DATEDIF(B2, TODAY(), "Y") Full Years (Most Popular) =INT(YEARFRAC(B2, TODAY(), 1)) Exact Leap-Year Adjusted =DATEDIF(B2,TODAY(),"Y") & " yrs, " & DATEDIF(B2,TODAY(),"YM") & " mos" Years + Months Combined Table of Contents
- 1. Why Excel Has No Native =AGE() Formula
- 2. Method 1: The DATEDIF Formula (Gold Standard)
- 3. Method 2: YEARFRAC with INT (Exact Fraction)
- 4. Method 3: Date Subtraction with 365.25
- 5. Advanced: Calculate Age in Years, Months & Days
- 6. Calculating Age as of a Specific Cutoff Date
- 7. Troubleshooting Common Errors (#NUM!, #VALUE!)
- 8. Formula Comparison Matrix
- 9. Frequently Asked Questions
Why Excel Has No Native =AGE() Function
Unlike modern programming languages or dedicated online calculators, Microsoft Excel does not provide an official =AGE() function. The reason stems from the calendar mathematics of human aging:
- Variable Month Lengths: Months span anywhere from 28 to 31 days.
- Leap Years: A year is not simply 365 days; astronomical solar years average approximately 365.2422 days.
- Legal vs. Mathematical Age: A person legally turns 30 on midnight of their birthday anniversary, regardless of how many leap days occurred in the intervening 30 years.
Because of these complexities, Excel provides specialized date arithmetic tools. Below are the three most reliable and widely accepted methods used by data analysts, HR specialists, and finance professionals.
Method 1: The DATEDIF Function (The Industry Gold Standard)
The =DATEDIF() (Date Difference) function is the most reliable formula for calculating age in full completed years.
SYNTAX:
=DATEDIF(start_date, end_date, unit) Understanding the Arguments:
- start_date: The birth date (e.g., cell
B2). - end_date: The current date, supplied dynamically with
TODAY(), or a fixed evaluation date. - unit: A text code specifying how the interval is returned.
| Unit Code | Description | Example Output (Born Jan 1, 2000) |
|---|---|---|
| "Y" | Full completed years | 26 |
| "M" | Total completed months | 318 |
| "D" | Total completed days | 9,680 |
| "YM" | Remaining months excluding completed years | 6 |
| "MD" | Remaining days excluding completed months | 14 |
DATEDIF in the autocomplete formula popup when you type =DATE...? That is because DATEDIF is an undocumented legacy function inherited from Lotus 1-2-3 for backwards compatibility. Don't worry: it is 100% supported in all modern Excel versions!
Method 2: The YEARFRAC & INT Function (Best for Fractional Ages)
If you need fractional age (such as actuarial calculations, sports divisions, or pediatric milestones like 4.5 years), =YEARFRAC() is ideal.
FORMULA FOR DECIMAL AGE:
=YEARFRAC(B2, TODAY(), 1)
The third parameter 1 instructs Excel to use the Actual/Actual basis, which accurately measures real elapsed days including February 29 leap days.
Converting to Whole Years with INT:
To convert the fractional year to an integer representing completed age without rounding up prematurely, wrap the function with =INT():
=INT(YEARFRAC(B2, TODAY(), 1)) Important: Always use INT() rather than ROUND(). A person who is 29.98 years old is still legally 29, not 30!
Method 3: Direct Date Subtraction with 365.25 (Fast Approximation)
Excel stores all dates as sequential serial numbers where 1 represents January 1, 1900. Therefore, subtracting birth date from today gives the exact number of days elapsed:
=INT((TODAY() - B2) / 365.25)
Dividing by 365.25 accounts for the four-year leap year cycle. While fast, this method carries a minor flaw: for individuals born on or near leap days, it can occasionally miscalculate completed age by 1 day during leap year boundaries.
Advanced: Calculate Exact Age in Years, Months & Days
To display an HR-ready or medical description (e.g. "32 Years, 4 Months, 12 Days"), combine three DATEDIF statements with text concatenation (&):
=DATEDIF(B2, TODAY(), "Y") & " Years, " & DATEDIF(B2, TODAY(), "YM") & " Months, " & DATEDIF(B2, TODAY(), "MD") & " Days" This formula dynamically extracts the completed years, remaining months, and remaining days down to the current date.
Calculating Age as of a Specific Cutoff Date
Often in school admissions, insurance underwriting, or retirement planning, you must calculate age as of a specific date (such as September 1st of the school year) rather than today.
Simply replace TODAY() with the DATE(year, month, day) function:
=DATEDIF(B2, DATE(2026, 9, 1), "Y") Troubleshooting Common Excel Age Errors
1. The #NUM! Error
Cause: start_date is greater than end_date (e.g., someone entered a future birthday).
Fix: Wrap with an IF condition:
=IF(B2>TODAY(), "Invalid Future Date", DATEDIF(B2, TODAY(), "Y"))
2. The #VALUE! Error
Cause: The date in cell B2 is formatted as text rather than a valid Excel date.
Fix: Select the column and apply Short Date format, or wrap with =DATEVALUE(B2).
3. Regional Semicolon Error (European Excel)
If your operating system uses commas as decimal separators, Excel formula arguments must be separated by semicolons (;) rather than commas:
=DATEDIF(B2; TODAY(); "Y")
Formula Comparison Matrix
| Formula | Primary Output | Leap Year Accuracy | Best Use Case |
|---|---|---|---|
| =DATEDIF(B2, TODAY(), "Y") | Completed Years | 100% Accurate | Official Records, HR, Passports |
| =INT(YEARFRAC(B2, TODAY(), 1)) | Completed Years | 100% Accurate | Financial, Actuarial, Decimals |
| =INT((TODAY()-B2)/365.25) | Approximate Years | Minor Edge Cases | Quick informal estimates |
Want exact age without typing Excel formulas?
Calculate your exact age down to milliseconds, exact days elapsed, completed heartbeats, astrological milestones, and planetary birthdays instantly in your browser.
Frequently Asked Questions (FAQ)
What is the most accurate formula to calculate age in Excel?
The =DATEDIF(birth_date, TODAY(), "Y") function is the gold standard for full completed years because it tracks true calendar months and leap years without rounding errors.
Why doesn't DATEDIF show up in Excel's autocomplete?
Microsoft keeps DATEDIF as an unlisted function preserved for Lotus 1-2-3 compatibility. Even though it doesn't appear in the autocomplete popup, it works across all editions of Microsoft Excel.
How can I calculate age on a specific future or past date?
Replace TODAY() with a date cell or the DATE function: =DATEDIF(B2, DATE(2026, 9, 1), "Y").
How do I fix the #NUM! error in Excel?
Verify that the birth date is earlier than the end date. A birth date set in the future will trigger #NUM!.