How to Calculate Age From a Date of Birth in Excel

Whether it's a staff list, a club membership or a class register, working out ages by hand is slow and goes out of date the moment someone has a birthday. One formula calculates everyone's age and keeps it up to date automatically.

With a date of birth in A2:

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

• A2 is the date of birth.
• TODAY() is today's date, which updates every time the file opens.
• "y" means count complete years.

Type it next to the first date of birth, press Enter, then double-click the fill handle to copy it down the column.

When you start typing =DATEDIF, Excel won't suggest it or show you the arguments. It's an old function Microsoft kept for compatibility and never added to the menus. It still works perfectly. Just type it out in full.

You might see this formula online:

=(TODAY()-A2)/365

It's close, but not reliable. Leap years mean it can be a day out, so someone can appear to turn a year older the day before their birthday, or the day after. DATEDIF counts real calendar years, so it's always right on the birthday itself.

If you do want a decimal age (like 34.6 years), use YEARFRAC, which handles leap years properly:

=YEARFRAC(A2, TODAY(), 1)

Wrap it in INT() to get whole years only.

To show something like 34 years, 7 months:

=DATEDIF(A2,TODAY(),"y")&" years, "&DATEDIF(A2,TODAY(),"ym")&" months"

The "ym" code gives the months left over after the complete years. Add "md" for leftover days if you need them.

Need someone's age on a set date, for example on 31 August for a school year, or on the date of an event? Replace TODAY() with the date or a cell that contains it:

=DATEDIF(A2, $E$1, "y")

where E1 contains the date you want. Lock it with $ signs (press F4) so it doesn't move when you copy the formula down.

You can also type the date directly using the DATE function: =DATEDIF(A2, DATE(2026,8,31), "y").

Once you have ages, you can sort them into bands with IFS (Microsoft 365 and Excel 2019+):

=IFS(B2<18,"Under 18", B2<30,"18–29", B2<50,"30–49", TRUE,"50+")

#VALUE! The date of birth is stored as text rather than a real date. If it sits on the left of the cell, see our guide on fixing date formats.

#NUM! The date of birth is later than today, often a typo in the year, or a US/UK date mix-up.

The age shows as a date (like 03/02/1900). Change the cell format to General or Number.

Blank rows show 126. Empty date cells count as 1900. Hide them with =IF(A2="", "", DATEDIF(A2, TODAY(), "y")).

=DATEDIF(A2, TODAY(), "y") is all you need. It's accurate on every birthday, updates itself every day, and works in every version of Excel, even though Excel won't admit it exists.