How to Calculate Age in Excel (3 Easy Methods)

Published: January 2027 · 5 min read

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 (Most Accurate)

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: Microsoft has never added a dedicated AGE function. DATEDIF is the closest alternative — it's undocumented but works in all versions.

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 them to actual dates first using DATEVALUE() or by changing the cell format to Date.

Q: Is DATEDIF available in Google Sheets?

A: Yes — Google Sheets supports DATEDIF with the same syntax.

← Back to Blog