DATEDIF in Excel: Calculate Age From a Date of Birth

Formulas & functions · Updated November 19, 2026

To calculate age from a date of birth in Excel, enter the birth date in A2 and use =DATEDIF(A2,TODAY(),"Y"). The formula returns completed years, so someone whose birthday has not yet arrived this year will not be counted as a year older.

The quick DATEDIF age formula

In Microsoft Excel for Microsoft 365 on Windows, put a valid date of birth in A2, then enter the formula in another cell. For example, if A2 contains July 20, 1990, the formula returns 36 on November 3, 2026. It updates automatically because TODAY() returns the current date.

DATEDIF calculates the difference between two dates. Its first argument is the earlier, starting date; the second is the later, ending date; and the third tells Excel what unit to return. For age in completed whole years, use the unit code Y. The function name may not appear in Excel’s formula autocomplete or function list, but you can type it directly into a cell.

The start date must be earlier than or equal to the end date. A future date of birth, a text value that Excel cannot read as a date, or reversed arguments can result in an error. The most reliable setup is to keep the birth date in one cell and use a clearly identified formula cell for the result.

What DATEDIF means and how it works

DATEDIF is a date-difference function retained in Excel for compatibility with older spreadsheet software. It is useful for age calculations because it counts completed calendar years rather than simply subtracting one year number from another. That distinction matters whenever the person’s birthday has not yet occurred in the current year.

For example, subtracting the birth year from the current year would make a person born in December appear a year older in January. DATEDIF with Y checks whether a full year has elapsed between the two dates. The result is an integer, not a decimal age.

The function accepts several unit codes, most often written in uppercase:

  • "Y" returns the number of complete years.
  • "M" returns the number of complete months in the whole interval.
  • "D" returns the total number of days between the dates.
  • "YM" returns the remaining months after complete years are removed.
  • "MD" returns the remaining days after complete months and years are removed.
  • "YD" returns the days between the dates as if they were in the same year.

For an ordinary age in years, Y is the appropriate choice. The other units are useful for elapsed-time calculations but can be misleading if used without understanding what portion of the interval they return.

Set up birth dates as real Excel dates

Excel needs to recognize the birth date as a date value, not just as text that happens to look like one. Enter dates in a format Excel recognizes under your regional settings, or use a consistent format such as 20-Jul-1990. If imported data is left-aligned or does not respond to date formatting, it may be stored as text; changing its appearance alone does not necessarily convert it.

To format a recognized date, select the cell and use Home > Number Format. Choosing Short Date or Long Date changes how the value is displayed, not the underlying date. If dates were imported as text, use Excel’s conversion tools or correct the source data before relying on an age formula. Be especially careful with ambiguous entries such as 03/04/2000, which can mean different dates in different regional settings.

For a simple worksheet, place the date of birth in A2 and the age formula in B2. Add headers such as Date of birth and Age in the first row, then fill the formula down for other people. A blank birth-date cell may produce an unwanted result or error, so consider whether blank rows should remain blank.

With the birth date in A2, use this formula in B2 to leave the result blank when A2 is empty; otherwise, it calculates completed years as of today: =IF(A2="","",DATEDIF(A2,TODAY(),"Y")). If your source cells may contain spaces or imported text rather than true blanks, clean or validate those entries too.

Calculate age on a chosen date

TODAY() is convenient for a live age list, but it changes as the calendar date changes. For a report that must remain reproducible, use a fixed as-of date in a separate cell. This makes it clear which date the age refers to and prevents a historical report from silently changing later.

Suppose A2 contains July 20, 1990, and B2 contains November 3, 2026. With those two dates in A2 and B2 respectively, enter this formula in C2: =DATEDIF(A2,B2,"Y"). It returns 36, because the person has had a birthday in 2026 by the stated as-of date.

You can also use a fixed date directly in a formula, but a separate as-of-date cell is easier to update and audit. With the birth date in A2 and the chosen reporting date in B2, the same formula can be copied down a list as long as each row has the corresponding dates. Label the reporting-date column so another reader does not mistake a historical age for a current one.

Show years, months, and days

Sometimes a whole-number age is not detailed enough. You can show completed years plus the remaining months and days by combining separate DATEDIF calculations. This is a descriptive elapsed interval, not necessarily the format a form or policy expects.

Assume A2 contains July 20, 1990, and B2 contains November 3, 2026. The years portion is calculated with =DATEDIF(A2,B2,"Y"), which returns 36. With the same A2 and B2 layout, the remaining months are calculated with =DATEDIF(A2,B2,"YM"), which returns 3. With those same dates, the remaining days are calculated with =DATEDIF(A2,B2,"MD"), which returns 14. Together, those results describe 36 years, 3 months, and 14 days.

DATEDIF’s MD unit has known edge-case behavior around some month-end dates, so be cautious when using it for contractual, billing, or other calculations where every day matters. For a simple display of someone’s age, the Y result is usually the relevant one. If you need an exact day count, use the D unit instead of treating the MD remainder as a general-purpose day-counting rule.

Common mistakes and special cases

  • Subtracting birth years: A formula based only on the year numbers can overstate age before the birthday. Use DATEDIF with Y for completed years.
  • Reversing dates: The birth date belongs first and the as-of date second. If the start date is later, DATEDIF can return a number error.
  • Using text instead of dates: Imported or ambiguous date strings can cause errors or incorrect interpretation. Confirm that Excel recognizes the value as a date.
  • Expecting a decimal: The Y unit returns whole completed years. It does not return, for example, 36.3 years. Use a different calculation only if a fractional year is specifically needed.
  • Using TODAY() in a saved report: A live formula recalculates, so yesterday’s report may show a different age later. Store the as-of date when historical results must remain fixed.

People born on February 29 need a little extra care when a task depends on the exact date a person is considered to have a birthday in a non-leap year. Rules or conventions can differ by purpose and location. If the result has legal, eligibility, or policy consequences, confirm the applicable rule and test the relevant dates rather than assuming a spreadsheet formula determines that rule.

DATEDIF compared with other approaches

DATEDIF with Y is generally the simplest formula for a conventional age in completed years. A plain subtraction of YEAR values is shorter but does not account for whether the birthday has passed. YEARFRAC can express a span as a fraction of a year, but its result depends on the selected day-count basis and is not the same thing as a person’s completed age. Do not substitute it without knowing how the receiving report expects fractional years to be defined.

For an age as of a past or future date, DATEDIF remains useful as long as the dates are valid and in the correct order. For a fixed display, use an as-of date in a cell. For a live roster, TODAY() is convenient. If a worksheet is used to determine eligibility or make a consequential decision, document the chosen date and rule, and verify the result against the governing requirements.

Frequently Asked Questions

Does DATEDIF work in Excel for Microsoft 365?
Yes. You can type DATEDIF directly into a worksheet formula in Excel for Microsoft 365. It may not be suggested by autocomplete, which does not mean the function is unavailable.

Why does my DATEDIF formula return #NUM!?
A common cause is that the start date is later than the end date. For an age calculation, put the date of birth first and the as-of date second. Also check that both cells contain valid Excel dates.

Can I calculate age as of a date other than today?
Yes. Put the chosen as-of date in a cell and use that cell as DATEDIF’s second argument. This is preferable for reports that need a fixed, auditable reference date.

How do I keep the age cell blank when there is no birth date?
With the birth date in A2, use =IF(A2="","",DATEDIF(A2,TODAY(),"Y")). This checks for an empty A2 before calculating age.

Why can’t I find DATEDIF in the function list?
DATEDIF is a compatibility function and may not appear in Excel’s autocomplete or function list. Enter the formula manually, including the three arguments and the quoted unit code.

Choosing the right age result

Use =DATEDIF(birth_date,as_of_date,"Y") when you need age in completed years. For a live worksheet, TODAY() supplies the as-of date; for a historical or repeatable report, use a dated cell instead. Check that the birth date is a genuine Excel date, keep the arguments in chronological order, and document any special rule that applies to leap-day birthdays. These small checks make an age calculation easier to trust and explain.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.