How to Calculate Age in Excel: A Complete Guide

Calculating age in Excel is a common task that comes up in everything from HR databases to birthday party planning. Whether you’re tracking employee ages, managing student records, or just curious about how old someone is, Excel offers several straightforward ways to get the answer. Let me walk you through the most effective methods.

The Simple Formula: DATEDIF

The easiest way to calculate age in Excel is using the DATEDIF function. This hidden gem calculates the difference between two dates in years, months, or days.

Here’s the basic formula:

=DATEDIF(birthdate, TODAY(), "Y")

Let’s break this down. If someone’s birthdate is in cell A2, your formula would look like this:

=DATEDIF(A2, TODAY(), "Y")

The function takes three arguments: the start date (birthdate), the end date (TODAY() gives you the current date), and “Y” which tells Excel you want the result in complete years. The result is the person’s current age.

Calculating Age as of a Specific Date

Sometimes you don’t want the current age, but rather how old someone was or will be on a particular date. Maybe you’re checking eligibility for a program that has age requirements as of January 1st, or you want to know someone’s age on their last birthday.

For this, replace TODAY() with the specific date:

=DATEDIF(A2, "12/31/2025", "Y")

Or reference another cell containing the date:

=DATEDIF(A2, B2, "Y")

Getting More Detailed: Years, Months, and Days

Need more precision than just years? You can calculate age in years and months, or even down to the exact day.

For years and months:

=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months"

This formula combines the years with the remaining months. The “YM” parameter gives you the months remaining after accounting for complete years.

For the complete breakdown including days:

=DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months, " & DATEDIF(A2, TODAY(), "MD") & " days"

The Alternative Method: YEARFRAC

If you prefer a decimal representation of age, YEARFRAC is your friend. This function calculates the fraction of a year between two dates.

=YEARFRAC(A2, TODAY())

This gives you results like 25.43 years. If you want just the whole number of years, wrap it in the INT function:

=INT(YEARFRAC(A2, TODAY()))

The Manual Calculation

You can also calculate age using a simple subtraction method:

=YEAR(TODAY()) - YEAR(A2)

However, this method has a significant flaw: it doesn’t account for whether the person has had their birthday yet this year. Someone born in December would show as a year older than they actually are if you check in January. For this reason, I recommend sticking with DATEDIF or YEARFRAC for accuracy.

Common Issues and How to Fix Them

#NUM! Error: This usually means your end date is before your start date. Double-check that the birthdate comes before the comparison date.

Dates showing as numbers: Excel stores dates as numbers. If you see something like 44562 instead of a date, the cell formatting is wrong. Right-click the cell, choose Format Cells, and select Date.

DATEDIF not showing in autocomplete: DATEDIF is an undocumented function in Excel, so it won’t appear in the function list. Just type it manually and it will work fine.

Practical Tips

When setting up age calculations in a spreadsheet, put the DATEDIF formula in one column and format it as a number. This makes it easy to sort by age, filter for specific age ranges, or calculate average ages across your dataset.

For recurring use, consider using TODAY() so the ages update automatically every day when you open the file. If you need ages to remain static (like for a historical record), use a fixed date instead.

Excel makes age calculation surprisingly simple once you know these formulas. Whether you need a quick calculation or a detailed breakdown, these methods will handle any age-related task you throw at them.