Excel Age from Date of Birth with DATEDIF Explained

Formulas & functions · Updated November 19, 2026

To calculate age in completed years in Excel, put the date of birth in A2 and enter =DATEDIF(A2,TODAY(),"Y") in another cell. DATEDIF counts full years between the two dates, so the result increases on the person’s birthday, not simply at the start of each calendar year.

What DATEDIF means for an age calculation

DATEDIF is a date function that returns the difference between a start date and an end date in a chosen unit. Its syntax is DATEDIF(start_date,end_date,unit). For age, the unit “Y” means complete years. If the birthday for the current year has not yet occurred, the function does not count that year as complete.

For example, someone born on June 15, 1990 is 36 on November 3, 2026. Someone born on November 30, 1990 is still 35 on that same date. A simple subtraction of birth year from current year would return 36 for both people, which is why it can be wrong by one year.

DATEDIF is supported in Excel, but it is a legacy function that does not appear in the usual function autocomplete or function list in the same way as many newer functions. You can still type it directly into a cell. The function name and unit argument must be spelled as shown, and the unit must be in quotation marks.

Set up the worksheet and enter the formula

For one person, enter a valid date of birth in A2. In B2, enter the formula below. Excel for Microsoft 365 on Windows uses the same formula syntax as Excel for Mac and Excel for the web.

With the date of birth in A2 and the current date supplied by Excel, enter =DATEDIF(A2,TODAY(),"Y") in B2. If A2 contains June 15, 1990 and the current date is November 3, 2026, the result is 36.

For a list, put each person’s date of birth in A2:A20 and enter the formula in B2. Copy it down using the fill handle, or select the destination cells and use Ctrl+D on Windows. On Mac, use Command+D. Each copied formula will refer to the corresponding row, such as A3 or A4.

To calculate age as of a chosen date rather than today, put that date in B1. With date of birth in A2 and the as-of date in B1, enter =DATEDIF(A2,$B$1,"Y") in C2. The dollar signs keep the reference to B1 fixed when the formula is filled down. If A2 is June 15, 1990 and B1 is November 3, 2026, the result is 36.

Use a fixed as-of date for consistent results

TODAY() returns the current date according to Excel’s calculation environment. The age formula can therefore produce a different answer on a later day, which is useful for a live roster but not always suitable for a report that needs to remain reproducible. TODAY() does not update every second; it updates when Excel recalculates the workbook, for example when the workbook is opened or recalculated.

For a report, audit, or batch of records, using one as-of date in a cell makes every row consistent and makes the calculation easier to explain later. You can type a date into B1, or use a date value generated with Excel’s DATE function. Avoid typing a date in an ambiguous form such as 03/04/2026 if users may interpret it differently by region. A cell formatted as a date still needs to contain a real Excel date value, not text that only looks like a date.

If a report is finalized and must never change, record the as-of date used, and consider copying the calculated results and using Paste Special > Values. This replaces formulas with their current results. Keep an original copy of the workbook if you may need to recalculate later.

Return years, months, and days

DATEDIF also supports “YM” for remaining months after complete years and “MD” for remaining days after complete months. With a birth date in A2 and today’s date as the endpoint, a display formula can combine those parts. With A2 holding June 15, 1990 and the current date November 3, 2026, the years and months components are 36 years and 4 months.

With the date of birth in A2, use =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months". That example displays “36 years, 4 months” on November 3, 2026. The ampersands join the numbers and text into one text string, so this result is intended for display rather than further numerical calculations.

You may see formulas that add a “MD” component to show days as well. Microsoft cautions that the “MD” unit can return an inaccurate result in some situations. For an exact number of elapsed days, subtract the start date from the end date instead. With the date of birth in A2 and a fixed as-of date in B1, use =$B$1-A2. For June 15, 1990 through November 3, 2026, the result is 13,289 days. Format the result as General or Number if Excel displays it as a date.

Check date values and prevent common errors

The most frequent cause of a DATEDIF problem is an invalid or unexpected date value. Excel stores dates as serial numbers, even though cells usually display them in a calendar format. Text imported from another system may look like a date without being recognized as one. If the formula returns #VALUE!, check whether the birth date and end date are actual Excel dates.

  • Format the date cell with Home > Number Format and choose a suitable date format. Changing the display format does not convert text into a date, but it can reveal whether Excel recognizes the value.
  • For dates imported as text, use a suitable import or conversion method and verify the result. Date interpretation can depend on regional settings, particularly for values where both the day and month are 12 or lower.
  • Make sure the end date is on or after the date of birth. DATEDIF can return #NUM! when the start date is later than the end date.
  • Check for blank cells before filling a formula through a large range. A blank birth date may be treated like an old serial date and produce a misleading age rather than an obvious warning.

For a list where empty birth-date cells should stay empty, with the date of birth in A2 and today as the endpoint, use =IF(A2="","",DATEDIF(A2,TODAY(),"Y")). A2 must contain a real date for the age calculation. This leaves B2 blank when A2 is blank; it does not validate every possible text or erroneous entry.

Handle future dates and unusual birthdays thoughtfully

A birth date accidentally entered in the future can cause an error or a nonsensical result, depending on the formula and the values involved. Check the source data rather than treating every formula error as a formatting issue. A separate validation rule can help prevent future dates from being entered, but an organization should choose a rule that fits its workflow, such as whether it permits estimated or incomplete dates.

Leap-day birthdays need a little care in applications where the exact legal or administrative age matters. A person born on February 29 has no February 29 birthday in a non-leap year; policies may treat the relevant anniversary as February 28 or March 1. DATEDIF calculates date intervals, but it does not encode a particular organization’s policy. Test the result around the relevant dates and apply the governing policy when a formal age determination is required.

Also distinguish age in completed years from other meanings of “age.” DATEDIF with “Y” does not give a decimal age such as 36.4, nor does it count the birth year as year one. For ordinary age reporting, counting full years is usually what people expect. For elapsed time, eligibility, or formal records, specify the endpoint and the counting rule explicitly.

DATEDIF compared with other approaches

Subtracting YEAR values, for example the current year minus the birth year, is simple but ignores whether the birthday has happened yet. It is unsuitable when the answer needs to be the person’s completed age. DATEDIF with “Y” handles that birthday check without requiring a longer formula.

A formula built from YEAR, MONTH, and DAY can also calculate completed years, but it takes more care to handle dates before the birthday. It may be useful when a workbook needs visible, separately controlled date logic or when a calculation must be explained without DATEDIF. For a basic age in full years, DATEDIF is usually more compact.

Another option is to use a manually selected reference date rather than TODAY(). That is not a different age method; it is a way to make the result stable and auditable. Choose a dynamic endpoint for a live directory, and a fixed endpoint for a dated snapshot. Whichever approach you use, document the date basis so that readers know why an age may differ from a later calculation.

Frequently Asked Questions

Does DATEDIF work in Excel for Microsoft 365?
Yes. Type =DATEDIF(A2,TODAY(),"Y") directly into a cell. DATEDIF may not appear in autocomplete, but that does not mean the function is unavailable.

Why does my Excel age formula show one year too few?
The birthday may not have occurred yet in the current year. DATEDIF with the “Y” unit counts complete years, so it returns the person’s age before their next birthday rather than simply subtracting birth years.

How do I calculate age on a specific date?
Put the as-of date in B1 and the birth date in A2. Use =DATEDIF(A2,$B$1,"Y"). The formula then uses the date in B1 instead of changing with TODAY().

Can DATEDIF calculate age in months?
Yes. Use the “M” unit for the total number of complete months between two dates. Use “YM” for the months remaining after complete years. These represent different values, so choose the one that matches the question you need to answer.

Why is DATEDIF not listed in Excel’s function suggestions?
DATEDIF is a legacy function and may not be offered in the usual autocomplete or function insertion list. Enter the function name and arguments manually, including quotation marks around the unit.

Choose the right age calculation

For a straightforward age in completed years, use DATEDIF with “Y” and a valid date of birth. Use TODAY() when the answer should update, or a fixed date cell when results need to stay consistent. Before sharing a workbook, check date values, blanks, and any special rules such as leap-day handling. Those small choices determine whether the displayed age answers the question the worksheet is meant to answer.

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.