What is the best formula to calculate age in Excel?

Use =DATEDIF(birth_date,TODAY(),"Y") to calculate completed years from a date of birth.

Why is my age formula wrong in Excel?

The most common causes are dates stored as text, regional date format issues, or formulas that subtract years without checking whether the birthday has passed.

Why does my formula show an error?

Open the linked error pages (#N/A, #VALUE!, #REF!) from our directory. Most formula posts fail because of data type mismatches, deleted references, or wrong match modes — not because the function name is wrong.

Should I copy formulas down or use a Table?

Copying down is fine for one-off models. Convert the range to an Excel Table when you add rows often — structured references stay readable and break less.

Formulas - 2026-05-12

How to Calculate Age in Excel

Calculate age in Excel from a date of birth using DATEDIF, YEARFRAC, TODAY, and formulas for years, months, days, and exact age labels.

Calculate Age from Date of Birth

The most common way to calculate age in Excel is to compare a birth date with today's date. If the date of birth is in A2: \\\ =DATEDIF(A2,TODAY(),"Y") \\\ This returns the number of completed years.

Age in Years, Months, and Days

To show a more detailed age label: \\\ =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days" \\\ This is useful for HR sheets, school records, memberships, and medical or service tracking.

Calculate Age on a Specific Date

Replace TODAY() with a fixed date or reference cell. \\\ =DATEDIF(A2,B2,"Y") \\\ This calculates age at the date in B2.

Approximate Age with YEARFRAC

YEARFRAC returns a decimal year difference: \\\ =YEARFRAC(A2,TODAY()) \\\ To return whole years: \\\ =INT(YEARFRAC(A2,TODAY())) \\\

Common Age Formula Problems

Birth dates stored as text instead of real dates Wrong regional date format Future dates entered by mistake Using simple year subtraction, which can be inaccurate before birthdays If a date is stored as text, convert it before calculating age.

Related Guides

DATEDIF function YEARFRAC function TODAY function Excel Date Functions Guide

Formula debugging workflow

Step Action 1 Select the cell and read the formula in the formula bar 2 Use Formulas → Evaluate Formula for nested functions 3 Check for text stored as numbers (green triangle) 4 Fix errors using our formula error guide Go deeper: Compare similar functions · Fix common problems

Frequently asked questions

What is the best formula to calculate age in Excel? Use =DATEDIF(birth_date,TODAY(),"Y") to calculate completed years from a date of birth.
Why is my age formula wrong in Excel? The most common causes are dates stored as text, regional date format issues, or formulas that subtract years without checking whether the birthday has passed.
Why does my formula show an error? Open the linked error pages (#N/A, #VALUE!, #REF!) from our directory. Most formula posts fail because of data type mismatches, deleted references, or wrong match modes — not because the function name is wrong.
Should I copy formulas down or use a Table? Copying down is fine for one-off models. Convert the range to an Excel Table when you add rows often — structured references stay readable and break less.