How to Calculate Age in Excel Formula?

• Birthdate: cell reference containing the date of birth

• “y”: unit code returning full years difference

Step‑by‑step example:

1. Enter your birthdate in A2 (06/15/1990).

2. In B2, type:

   =DATEDIF(A2, TODAY(), “y”)

3. Press Enter. Excel returns 35 (as of July 29, 2025).

2. Show Age in Years, Months, and Days

Want a breakdown like “35 years, 2 months, 14 days”? Use three DATEDIF calls:

=DATEDIF(A2, TODAY(), “y”) & ” years, ” &
DATEDIF(A2, TODAY(), “ym”) & ” months, ” &
DATEDIF(A2, TODAY(), “md”) & ” days”

• “ym”: months excluding years

• “md”: days excluding months and years

Real‑world mini case study:
An HR team at Acme Corp. tracks employee anniversaries in a spreadsheet. By adding the above formula in the “Tenure” column, each employee’s service duration displays as “X years, Y months, Z days,” boosting accuracy in benefits calculations.

How to Create a Workbook in Excel for the Web: Step-by-Step Guide

3. Quick Year‑Only Calculation with YEAR and TODAY

For a lightweight alternative:

=YEAR(TODAY()) – YEAR(A2)

• Pros: Simple

• Cons: Doesn’t account for whether the birthday has occurred this year

To adjust for birthdays not yet reached:

=YEAR(TODAY()) – YEAR(A2) – (DATE(YEAR(TODAY()), MONTH(A2), DAY(A2)) > TODAY())

This subtracts an extra year if the birthday is still upcoming.

4. Decimal Age with YEARFRAC

If you need age as a decimal (e.g., 35.21 years):

=YEARFRAC(A2, TODAY(), 1)

• Basis 1: Actual days/actual days in year

• Returns a precise fractional year value—helpful in actuarial or scientific contexts.

5. Common Pitfalls & Troubleshooting

• #NUM! Errors: Occur if the start date is after the end date. Ensure Birthdate ≤ TODAY().

• Text‑formatted dates: Excel won’t recognize “15 June 1990” unless you convert it via Data → Text to Columns or wrap with DATEVALUE.

• Regional date settings: Formats like dd/mm/yyyy vs. mm/dd/yyyy can trigger #VALUE!. Confirm system settings under Control Panel → Region.

Key Takeaways

• DATEDIF is the go‑to function for precise age in years, months, or days Microsoft Support.

• Combine DATEDIF segments to display “years, months, days.”

• YEAR + TODAY offers a simple year‑only age but needs adjustment for upcoming birthdays.

• YEARFRAC returns a decimal age—ideal for scientific or actuarial use.

• Watch out for date formatting issues and Excel’s regional date settings.

5 Free Excel Spreadsheet Tools for Your Day to Day Financial Needs

FAQs

Q1: What’s the best way to calculate age in months only?
Use =DATEDIF(Birthdate, TODAY(), "m") for total months between two dates.

Q2: Why does DATEDIF sometimes return incorrect results?
DATEDIF is a legacy Lotus 1‑2‑3 support function. Rare edge‑case bugs exist—always test with sample data Microsoft Support.

Q3: How can I prevent my age formula from recalculating every day?
Copy the result and use Paste Special → Values to “freeze” the age at that moment.

Q4: Can I calculate future age (age at a specific date)?
Yes—replace TODAY() with your target date cell, e.g., =DATEDIF(Birthdate, C2, "y").