XLS LibraryXLS Library
Excel Formulas & Functions

Age Formula in Excel: Calculate Accurate Ages reliably

Master age calculations in Excel with date math. This guide covers DATEDIF and YEARFRAC for years, months, and days, plus tips for leap years and data validation to keep results reliable.

XLS Library
XLS Library Team
·5 min read
Data ValidationData Analysis BasicsExcel ShortcutsExcel FormulasFormula Tips
Age Formula Excel - XLS Library
Photo by George Dolgikhvia Pexels
Quick AnswerDefinition

An age formula excel typically computes age from a birth date using date math. The standard approach uses TODAY() to compare dates with DATEDIF, or YEARFRAC for precise fractions. For full years: =DATEDIF(BirthDate, TODAY(), "Y"); to include months or days, extend with additional DATEDIF parts or YEARFRAC-based calculations. This method is robust across date formats when data is clean.

Core formulas for age in Excel

Calculating age from a birth date relies on date arithmetic rather than counting calendar days manually. The most reliable approach uses DATEDIF with TODAY() to determine completed years. In practice, you can start with a simple formula such as:

Excel Formula
=DATEDIF(BirthDate, TODAY(), "Y")

This returns the number of full years between BirthDate and today. The key is to treat BirthDate as a date value rather than text. The function takes three arguments: start_date (BirthDate), end_date (TODAY()), and unit ("Y" for years).

To show additional detail, you can compute months after the last completed year:

Excel Formula
=DATEDIF(BirthDate, TODAY(), "YM")

Or combine years and months:

Excel Formula
=DATEDIF(BirthDate, TODAY(), "Y") & " years, " & DATEDIF(BirthDate, TODAY(), "YM") & " months"

For fractional years, YEARFRAC provides a decimal representation, which you can round or floor as needed:

Excel Formula
=YEARFRAC(BirthDate, TODAY(), 1)

To get an integer age from YEARFRAC, use:

Excel Formula
=INT(YEARFRAC(BirthDate, TODAY(), 1))

Edge cases exist. If BirthDate is blank or invalid, wrap with IFERROR:

Excel Formula
=IFERROR(DATEDIF(BirthDate, TODAY(), "Y"), "")

Note: All examples assume BirthDate is a proper Excel date and that you are working in a modern Excel environment (desktop or online).

-1

Steps

Estimated time: 25-40 minutes

  1. 1

    Prepare your data

    Create a BirthDate column with proper date values. Ensure there are no text dates and that cells are formatted as dates. This foundation prevents miscalculations caused by data type mismatches.

    Tip: Use Data -> Text to Columns to convert strings to dates if needed.
  2. 2

    Enter the core age formula

    In a new cell, reference the birth date and apply the DATEDIF with TODAY() to get age in years. Use a single birth date per calculation for clarity.

    Tip: Start with =DATEDIF(BirthDate, TODAY(), "Y").
  3. 3

    Extend for months or days

    If you need more precision, add additional DATEDIF parts to show months after years or days after the last full month.

    Tip: Combine with & to join text, e.g., "& DATEDIF(...) & " months"".
  4. 4

    Copy across your dataset

    Fill down the formula to apply to multiple rows. Consider using a table so formulas auto-fill with new data.

    Tip: Double-click the fill handle to auto-fill down until data ends.
Pro Tip: Use TODAY() to keep ages up-to-date without manual edits.
Warning: Ensure BirthDate is a valid date; text dates may yield incorrect results.
Note: IFERROR helps keep your sheet clean when data is missing.
Pro Tip: Convert your data range to a Table to auto-propagate formulas with new rows.

Prerequisites

Required

Optional

  • Ability to drag formulas across a range or convert to a table
    Optional

Keyboard Shortcuts

ActionShortcut
Enter a new age formulaEnter to commit an array formula? Prefer simple single-cell entry hereCtrl+↵

People Also Ask

What is the best age calculation method in Excel?

The most reliable approach is DATEDIF with TODAY() to return full years. For more detail, add YM or MD to show months and days. YEARFRAC provides decimal years for precision. Always validate inputs and handle blanks with IFERROR.

Use DATEDIF with TODAY() for robust age calculations; add YM or MD for more detail and use IFERROR for blanks.

How do I calculate age in months?

To calculate age in months after the full years, use DATEDIF with the YM unit: =DATEDIF(BirthDate, TODAY(), "YM"). Combine with the year component if needed: =DATEDIF(BirthDate, TODAY(), "Y") & " years, " & DATEDIF(BirthDate, TODAY(), "YM") & " months".

Use DATEDIF with YM to get months after the full years, and combine with the years if you want a full statement.

Why do I get #NUM! in DATEDIF?

DATEDIF can return #NUM! if BirthDate is blank or later than today, or if dates are invalid. Use IFERROR to clean up results and ensure inputs are proper dates.

#NUM! usually means a bad date; check that the birth date is a real date and not after today.

Can I auto-fill ages for an entire column?

Yes. enter the formula once, then fill down or pull the fill handle to apply to the entire column. Using a table or structured references makes this routine easier as you add rows.

Yes—just drag the formula down or turn your data into a table for automatic expansion.

What about leap years in age calculations?

DATEDIF and YEARFRAC handle leap years correctly when dates are valid. For edge cases (Feb 29 births), test with a birth date on February 29 to verify consistency.

Leap years are handled by the date math, but it's good to test edge cases like Feb 29 births.

The Essentials

  • Use DATEDIF with TODAY() for full-year age
  • Add YM/MD units for months and days
  • Wrap with IFERROR for empty values
  • Validate data types before computing ages
  • Convert to a Table to scale formulas

Related Articles

← More in Excel Formulas & Functions