Spreadsheet formulas have edge cases. Check the chosen reference date and consult Microsoft’s DATEDIF documentation, especially the warning about the MD unit.
How to Calculate Age in Excel (3 Easy Methods)
Excel is one of the most powerful tools for calculating age. Whether you're managing employee data, student records, or customer databases, these 3 methods will help you calculate age accurately.
Method 1: DATEDIF Function (Check Edge Cases)
The DATEDIF function calculates the difference between two dates in years, months, or days.
Formula for Full Age
Result: 31 Years, 1 Months, 7 Days
Formula for Age in Years Only
Result: 31
DATEDIF Unit Codes
"Y"— Complete years"M"— Complete months"D"— Complete days"YM"— Months after subtracting years"MD"— Days after subtracting months"YD"— Days after subtracting years
Note: Replace A1 with the cell containing the date of birth.
Method 2: YEARFRAC Function (Age in Decimal)
The YEARFRAC function gives age as a decimal number. Use INT to get the whole number.
Result: 31
Advantage: Simpler formula. Disadvantage: May have small rounding differences.
Method 3: Simple Subtraction (Quickest)
For rough age calculation, just subtract birth year from current year:
Result: 31
Warning: This doesn't account for whether the birthday has passed this year. Use only for estimates.
Calculate Age on a Specific Date (Not Today)
Instead of TODAY(), use any date cell:
Where A1 is the DOB and B1 is the cut-off date (like 1 August 2027).
Common Excel Age Calculation Errors
- #NUM! error: DATEDIF returns this if the start date is after the end date.
- Wrong date format: Excel may read dates differently depending on your region.
- Cell format: The result cell must be formatted as "General" or "Number", not "Date".
Frequently Asked Questions
Q: Why doesn't Excel have an AGE function?
A: DATEDIF is documented by Microsoft. Its MD unit has known limitations; review Microsoft’s guidance and check month-boundary cases before using a detailed age formula.
Q: Can I calculate age for a whole column at once?
A: Yes — enter the formula in the first cell, then drag the fill handle down to apply it to all rows.
Q: What if my dates are in text format?
A: Convert text to genuine date values using a suitable conversion such as DATEVALUE, with the correct regional interpretation. Changing display formatting alone does not necessarily convert text.
Q: Is DATEDIF available in Google Sheets?
A: Yes — Google Sheets supports DATEDIF with the same syntax.
Calculate Age Instantly →