Excel Formula for Age at a Certain Date Using DATEDIF

To calculate someone’s age on a particular date in Excel, use =DATEDIF(A2,B2,"Y"), where A2 contains the birth date and B2 contains the date to calculate their age on. The result is the number of complete years they had reached by that date.

The basic age formula

Excel’s DATEDIF function compares two dates and returns the difference in the unit you specify. For age, the unit is “Y”: it counts whole years, not a fraction of a year or the difference between the dates’ year numbers. This makes it suitable for questions such as “How old was this person on 1 November 2026?”

Assuming A2 contains a person’s date of birth and B2 contains the date you want to check, enter this in another cell:

=DATEDIF(A2,B2,"Y")

If A2 is 15 June 1990 and B2 is 1 November 2026, the result is 36. The person turned 36 on 15 June 2026, so they had completed 36 years by the date in B2. If the check date were 1 June 2026 instead, the result would be 35 because their birthday that year had not yet occurred.

How to set up the dates in Excel

Put the birth date and the date being checked in separate cells. Keeping the dates in cells, rather than typing them directly into a formula, makes the calculation easy to update and copy down for a list of people.

Enter each birth date in column A, starting in A2.
Enter the date to calculate age on in B2. You can use the same check date for every person.
Enter the DATEDIF formula in C2, then fill it down for other rows.
Format the result cells as Number or General if Excel displays an unexpected date format.

For a list with birth dates in A2:A100 and one shared check date in B1, use B1 in the formula and keep that reference fixed when copying down. Assuming the birth date is in A2 and the shared check date is in B1, enter this in C2:

=DATEDIF(A2,$B$1,"Y")

The dollar signs keep the check-date reference at B1 as the formula is filled down; A2 changes to A3, A4, and so on. On Windows, use Ctrl+C and Ctrl+V to copy and paste, or drag the fill handle from the lower-right corner of the selected cell. On Mac, use Command+C and Command+V; the fill handle works similarly.

What DATEDIF counts—and what it does not

DATEDIF with the “Y” unit returns completed years. It does not round a partial year up. A person who is 17 years and 11 months old therefore has an age of 17, not 18. This is usually the intended meaning of age in years, but it matters if a report needs a different measure.

DATEDIF is a legacy Excel function. It works in Microsoft 365 for Windows, but it may not appear in Excel’s autocomplete or function suggestions while you type. That does not mean the function is unavailable: enter its name and arguments manually. The function is also available in Excel for Mac and Excel for the web, although interface details can vary.

Do not substitute a simple subtraction of the year values. For example, subtracting the birth year from the check-date year gives 36 for a person born late in 1990 when checking early in 2026, even though that person may still be 35. DATEDIF accounts for whether the birthday has happened yet that year.

Choosing the date you want to calculate against

A certain-date calculation is different from calculating someone’s age today. Referencing a date in a cell makes the answer reproducible: if you later open the workbook, the result still reflects the chosen date. This is useful for historical records, eligibility checks, event planning, and reports prepared for a specific cutoff.

If you specifically want age as of today, Excel’s TODAY function can supply the second date. Assuming A2 contains the birth date and the formula is entered in another cell, use:

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

TODAY updates when Excel recalculates the workbook, so the result can change from one day to another. That is useful for a live age list, but not ideal when you need a fixed historical snapshot. For a report that must retain its original cutoff, enter that cutoff date in a cell and refer to it instead.

Leap-day birthdays and anniversary conventions

A person born on 29 February has a birthday only in leap years. In a non-leap year, different organisations or jurisdictions may use different rules for deciding whether the person’s age changes on 28 February or 1 March. The appropriate convention can depend on the purpose of the calculation, so Excel cannot determine which rule your situation requires.

DATEDIF compares the month and day when determining completed years. As a result, for a 29 February birth date, its completed-year result generally changes on 1 March in a non-leap year rather than on 28 February. If a policy or official rule treats 28 February as the anniversary instead, the basic DATEDIF result may not match that policy. Confirm the required convention before using the number for an eligibility or compliance decision.

This distinction is easy to miss because most birthdays fall on a date that exists every year. If leap-day cases matter, include a test row with a 29 February birth date and check results immediately before and after the chosen anniversary. Record the convention alongside the workbook so another person interpreting the results knows what the calculation means.

Common errors and how to fix them

#NUM! Usually means the start date is later than the end date, such as a birth date after the check date. Verify that the two cells are in the right order and that neither date was entered incorrectly.
#VALUE! Can occur if a cell contains text that Excel cannot interpret as a date. Re-enter the value as a real date rather than text that only looks like one.
A surprising number or a date display: The formula result should be a number of years. If Excel displays a calendar-style value, change the result cell’s number format to General or Number.
Wrong result after copying: Check cell references. If all rows should use one cutoff date, make that reference absolute with dollar signs, as in $B$1.

Date entry can be affected by regional settings. For example, 03/04/2026 can mean 3 April or March 4, depending on the date convention Excel is using. If a date is ambiguous, enter it using a format your installation clearly recognizes, or build it with Excel’s DATE function. You can also inspect the cell’s number format, but formatting alone does not turn text into a valid date.

For a workbook that may contain blanks or dates entered in the wrong order, you can prevent the formula from showing an error or a misleading result. Assuming A2 is the birth date and B2 is the check date, enter this in the result cell:

=IF(OR(A2="",B2=""),"",IF(B2<A2,"Check dates",DATEDIF(A2,B2,"Y")))

This returns a blank if either date cell is empty, displays “Check dates” if the check date precedes the birth date, and otherwise returns completed years. It does not verify that an entered value is a genuine date, so data validation or a review of the source data may still be needed.

Why not use YEARFRAC or subtract years?

YEARFRAC calculates a fraction of a year between two dates. That can be useful when you need an elapsed-year estimate, but its result depends on the chosen basis and is not the same thing as completed age. Rounding a YEARFRAC result can also give the wrong birthday-based age near an anniversary. For ordinary age in whole years, DATEDIF with “Y” states the intended measure directly.

Subtracting YEAR(birth date) from YEAR(check date) is simpler but ignores the month and day. It overstates age whenever the birthday has not yet occurred in the check year. DATEDIF avoids that particular problem without requiring a separate birthday comparison. If a report needs age in months or days instead, use a method designed for that unit rather than interpreting the whole-year result as a precise duration.

Frequently Asked Questions

Can I calculate age on a past date?
Yes. Put the historical date in the second date cell and use it as the end date in DATEDIF. The formula calculates age as of that date, even if it is earlier than today.

Can the check date be the same for every person?
Yes. Put the shared date in one cell and use an absolute reference such as $B$1 when filling the formula down. This keeps the cutoff fixed while the birth-date reference changes by row.

Why does DATEDIF not appear in Excel’s function suggestions?
DATEDIF is a legacy function and may not be listed in autocomplete. It can still be entered directly in a formula in current Excel versions.

Does the formula calculate age in months too?
The “Y” unit returns complete years. DATEDIF has other units for date intervals, but a months-only interval is not the same as a conventional age expressed as years and remaining months. Choose the output you actually need rather than treating those measures as interchangeable.

Will the result change automatically?
Only if a referenced date changes or the formula uses a changing value such as TODAY(). A fixed date typed in a cell stays fixed, so the calculated age remains tied to that cutoff.

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.