Excel Formula for Age From DOB: Years, Months, and Days

To calculate age in completed years from a date of birth in cell A2, use =DATEDIF(A2,TODAY(),"Y"). It compares the birth date with today and returns the number of full years; for age on a particular date instead, use that date in a second cell.

The basic age formula

Enter a person’s date of birth in A2, then put the formula in another cell, such as B2. The result is a whole number: someone who has turned 35 but has not yet reached 36 gets an age of 35. DATEDIF is useful here because its Y unit counts completed years rather than simply subtracting the two calendar years.

With the date of birth in A2 and the current date supplied automatically by Excel, enter:

=DATEDIF(A2,TODAY(),"Y")

TODAY() returns the current date according to the computer’s date settings. The formula updates when Excel recalculates the workbook, so the age can change on a later date without you editing the formula. If you need a record that must not change over time, use a fixed as-of date in a cell instead.

For example, suppose A2 contains November 3, 2000, and the calculation date is November 2, 2026. The result is 25, not 26, because the birthday has not arrived yet that year. On November 3, the result becomes 26.

How DATEDIF calculates completed years

DATEDIF uses a start date, an end date, and a unit that tells Excel what difference to return. For an age calculation, the birth date is the start date and the date being compared is the end date. The Y unit returns full years between those dates, which is why it handles a birthday that has not yet occurred in the current year.

DATEDIF is an older Excel function retained for compatibility. It may not appear in Excel’s function autocomplete or formula suggestions, but it can still be entered directly in Microsoft 365 for Windows. Its syntax is DATEDIF(start_date,end_date,unit). The end date must not be earlier than the start date; a future birth date can produce a #NUM! error.

Other units can return different parts of the date interval:

"Y" returns complete years.
"M" returns complete months across the entire interval.
"D" returns the total number of days.
"YM" returns remaining months after complete years are removed.
"YD" returns days after complete years are removed.

For a straightforward age, Y is the important unit. The other units are useful for an age breakdown, but they should be chosen with care; a total number of days is not the same thing as years of age divided by a fixed number of days.

Calculate age on a chosen date

A fixed comparison date is useful for reports, eligibility checks, historical records, or testing a formula. Put the birth date in A2 and the date to calculate age on in B2. Then use:

=DATEDIF(A2,B2,"Y")

For instance, if A2 is June 15, 1990, and B2 is November 2, 2025, the result is 35. This result stays the same even when the workbook is opened next year, unless someone changes B2. Using a dedicated as-of-date cell also makes a worksheet easier to audit: a reader can see the date used in the calculation rather than relying on the day the file happened to recalculate.

To make the as-of date reflect today while keeping it visible, put =TODAY() in B2 and use the DATEDIF formula above. With the birth date in A2 and B2 as the date cell, that setup calculates current age while making the comparison date explicit. If you need a historical or frozen report, replace the formula in B2 with a fixed date value.

Show years, months, and days

Sometimes a whole-number age is not detailed enough. With a birth date in A2 and an as-of date in B2, this formula displays completed years, remaining months, and remaining days as text:

=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&(B2-EDATE(A2,DATEDIF(A2,B2,"Y")*12+DATEDIF(A2,B2,"YM")))&" days"

For example, a birth date of June 15, 1990, and an as-of date of November 2, 2025, produce 35 years, 4 months, 18 days. The years and months identify the last completed month anniversary; EDATE calculates that anniversary date, and subtracting it from B2 gives the remaining days. The displayed phrase is text, not a numeric value, so it is best for labels and reports rather than further arithmetic.

The shorter approach often seen online uses DATEDIF with the MD unit for the final days. Microsoft warns that MD can produce an incorrect result in some situations. Calculating the last completed month with EDATE and subtracting that date avoids relying on MD. As with any age breakdown, test important boundary dates—especially month-end and leap-day birthdays—and use a clear policy if the result has formal significance.

Choose the right method for your worksheet

There are several ways to express an age-like value, but they answer different questions. Use the method that matches what the output is meant to represent:

Completed age in years: DATEDIF with Y is usually the clearest choice. It returns the age people ordinarily mean when they say someone is 35.
Age on a past or future date: Use DATEDIF with a date stored in a cell rather than TODAY(). This makes the comparison date controllable and visible.
Approximate age as a decimal: YEARFRAC can express a date interval as a fraction of a year, but it is not the usual answer for age in whole birthdays. Its result depends on the selected day-count basis.
Elapsed days: Subtract the birth date from the comparison date to get days. This may help with elapsed-time reporting, but it does not directly produce age in years because calendar years differ in length.

A common but misleading shortcut is to subtract birth year from current year. With the birth date in A2, this expression illustrates that shortcut:

=YEAR(TODAY())-YEAR(A2)

It returns 26 for a person born on November 3, 2000, on November 2, 2026, even though that person is still 25. It compares year numbers and ignores the month and day. DATEDIF with Y avoids that off-by-one result for ordinary age calculations.

Set up dates and handle common errors

Excel needs actual date values for reliable date arithmetic. A cell may look like a date while actually containing text—for example, because data was imported from another system. If DATEDIF returns an error or an unexpected result, check that both the birth date and comparison date are recognized as dates. In Excel for Microsoft 365 on Windows, select the cells and inspect the Number Format list on the Home tab; applying a date format changes how a date value appears, though it does not convert every text value into a true date.

After entering the formula, format the result cell as General or Number if it displays as a date. Excel stores dates as serial numbers, and a result cell carrying a date format can therefore show an age as an apparently strange calendar date. To change it, select the result cell and choose Home > Number Format > General, or choose Number.

Typical problems and checks include:

#NUM!: The end date may be earlier than the birth date, or the birth date may be in the future.
#VALUE! or an implausible answer: One of the input cells may contain text rather than a valid date, or a date may have been interpreted using a different regional order for day and month.
A date appears instead of an age: Change the formula cell’s number format to General or Number.
The result is one year too high: Confirm that the formula uses DATEDIF with Y and that the end date is the intended comparison date.
The age changes unexpectedly: TODAY() updates as the workbook recalculates. Use a fixed date in a cell when the calculation must remain tied to a particular day.

For a list where some rows are blank or may contain a future date, a guarded formula can prevent an empty row from displaying an age and flag a future birth date. With the date of birth in A2, use:

=IF(A2="","",IF(A2>TODAY(),"Check DOB",DATEDIF(A2,TODAY(),"Y")))

This returns a blank when A2 is empty, the words Check DOB when the date is later than today, and otherwise the completed age. Since the formula can return either a number or text, downstream calculations should account for the text values.

Leap-day birthdays and interpretation

A person born on February 29 has a birthday date that does not appear in most calendar years. DATEDIF can calculate an age interval, but a workbook formula cannot decide which birthday convention a particular organization or rule intends for a non-leap year. Some contexts treat the relevant date as February 28, while others use March 1. If the exact date matters, define the convention first and test the formula against dates immediately before and after that birthday.

The same care applies to age thresholds used in official, contractual, or eligibility settings. An Excel calculation is a date comparison, not a determination of which rule applies. Keep the source birth date accurate, make the as-of date explicit, and verify the governing definition before relying on the calculated age for a consequential decision.

For everyday lists, the standard DATEDIF formula is usually sufficient. The point is not that every leap-day entry needs a special formula, but that an edge case should not be left to an unstated assumption when a consistent policy is required.

Frequently Asked Questions

What is the Excel formula for age from date of birth?
With the date of birth in A2, use =DATEDIF(A2,TODAY(),"Y"). It returns the number of completed years as of today.

How do I calculate age from DOB in Excel on a specific date?
Put the date of birth in A2 and the comparison date in B2, then use =DATEDIF(A2,B2,"Y"). This is useful for a fixed report date or historical calculation.

Why is DATEDIF missing from Excel’s function list?
DATEDIF is retained for compatibility and may not appear in autocomplete or the function suggestions. Type the formula directly; the function can still be used in Microsoft 365 for Windows.

Why does my age formula show a date?
The result cell may be formatted as a date. Select it and choose Home > Number Format > General or Number to display the returned age as a number.

Can I calculate age in years, months, and days?
Yes. Use the longer formula in the years, months, and days section with the birth date in A2 and the as-of date in B2. It returns a text description; check month-end and leap-day cases if your output must follow a specific convention.

Does the formula update automatically on a birthday?
A formula using TODAY() updates when Excel recalculates the workbook, so the completed-year result can increase on the birthday. A formula based on a fixed date in B2 does not advance unless that date is changed.

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.