How to Calculate Age from Birthdate in Excel - Practical Guide

Learn to calculate age from a birthdate using Excel formulas. This tutorial covers the DATEDIF function and alternative methods to automatically compute exact age in years, months, or days. Perfect for HR, healthcare, and demographic data management.

Why This Matters

Calculating age automatically saves time in HR, healthcare, and customer management while ensuring accuracy for compliance, eligibility checks, and statistical analysis.

Prerequisites

•Basic understanding of Excel cell references (A1, B1)
•Familiarity with formula entry and the formula bar
•Access to Excel 2016 or later

Step-by-Step Instructions

Enter the birthdate in a cell

Click on a cell (e.g., A2) and enter a birthdate in format MM/DD/YYYY or DD/MM/YYYY depending on your locale. Press Enter to confirm.

Click on the cell for age calculation

Select the adjacent cell (e.g., B2) where you want the age result to appear.

Enter the DATEDIF formula

Type the formula =DATEDIF(A2,TODAY(),"Y") to calculate age in complete years. TODAY() returns the current date, and "Y" specifies years as the unit.

Press Enter to execute the formula

Hit Enter to calculate the age. The result displays the person's current age in whole years.

Copy the formula to other rows

Click B2, copy (Ctrl+C), select the range B3:B10, and paste (Ctrl+V) to apply the formula to multiple birthdates at once.

Alternative Methods

YEARFRAC function

Use =INT(YEARFRAC(A2,TODAY())) to calculate age with decimal precision, then round to whole years. This method works across all Excel versions.

YEAR and MONTH calculation

Use =YEAR(TODAY())-YEAR(A2)-IF(OR(MONTH(TODAY())<MONTH(A2),AND(MONTH(TODAY())=MONTH(A2),DAY(TODAY())<DAY(A2))),1,0) for precise age accounting for birthdate not yet reached this year.

Age in months or days

Replace "Y" in DATEDIF with "M" for months or "D" for days: =DATEDIF(A2,TODAY(),"M") or =DATEDIF(A2,TODAY(),"D").

Tips & Tricks

✓Ensure birthdates are formatted as date values, not text, by using Format Cells > Number > Date if calculations fail.
✓Use TODAY() function to ensure age updates automatically each day without manual formula changes.
✓For past ages or specific reference dates, replace TODAY() with a fixed date like DATE(2023,12,31).

Pro Tips

★Create a helper column displaying "Age: X years, Y months" using concatenation: =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months".
★Use data validation to restrict birthdate entries to realistic dates and prevent formula errors from invalid inputs.
★For bulk age calculations, apply conditional formatting to highlight ages in specific ranges (e.g., retirement-eligible employees).

Troubleshooting

Formula returns #NAME? error

DATEDIF may not be recognized in some Excel versions. Use the YEARFRAC alternative: =INT(YEARFRAC(A2,TODAY())). Ensure you're using Excel 2016 or later.

Age calculation is off by one year

This usually means the person's birthday hasn't occurred yet this year. Use the YEAR/MONTH formula that accounts for this: =YEAR(TODAY())-YEAR(A2)-IF(OR(MONTH(TODAY())<MONTH(A2),AND(MONTH(TODAY())=MONTH(A2),DAY(TODAY())<DAY(A2))),1,0).

TODAY() function shows an outdated date

TODAY() is a volatile function that updates automatically; press Ctrl+Shift+F9 to force recalculation of all formulas in the workbook.

Related Excel Formulas

DATEDIF TODAY YEARFRAC YEAR MONTH DAY DATE

Frequently Asked Questions

What's the difference between DATEDIF and YEARFRAC?

DATEDIF returns exact whole units (years, months, days) and is simpler for age calculation, while YEARFRAC returns a decimal fraction of years. DATEDIF is preferred for age in most cases, but YEARFRAC is more universally compatible across Excel versions.

Can I calculate age from a specific date instead of TODAY()?

Yes, replace TODAY() with any date using the DATE function: =DATEDIF(A2,DATE(2023,12,31),"Y") calculates age as of December 31, 2023. This is useful for historical age calculations or reports.

How do I show age in years and months together?

Use concatenation with two DATEDIF functions: =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months". The "YM" unit shows remaining months after accounting for full years.

Why is my age calculation showing a negative number?

A negative result means the birthdate is in the future or after your reference date. Double-check that the birthdate is earlier than TODAY() or your specified end date.

Does DATEDIF work in Excel Online?

DATEDIF is supported in Excel Online and Excel 365. For older versions, use YEARFRAC as a fallback that has broader compatibility across all platforms.