Calculate Age in Excel for a Specific Date

To calculate age in Excel on a particular date, put the birth date in A2 and the date to calculate against in B2, then use =DATEDIF(A2,B2,"Y"). The result is the number of full years completed by the date in B2, so the calculation does not count someone’s next birthday before it happens.

How the age formula works

DATEDIF compares two dates and returns the difference in a unit you specify. In this case, A2 is the earlier date of birth, B2 is the later as-of date, and the unit "Y" asks for completed years. It is useful for age calculations because simply subtracting birth year from the as-of year can be one year too high if the birthday has not yet occurred that year.

For example, with a birth date of June 15, 1990, in A2 and November 2, 2026, in B2, the formula returns 36. The person has already had their 2026 birthday. If the as-of date were March 2, 2026, it would return 35, because June 15 has not yet arrived.

DATEDIF is an older Excel function that remains available in Microsoft 365, although it may not appear in the formula autocomplete list. Enter it directly in a cell. The formula works in Excel for Windows, Mac, and the web; the examples here use the usual comma separators, though regional settings can affect the separator Excel expects.

Set up the dates correctly

Put the two dates in separate cells so that the calculation is easy to audit and reuse. A simple layout is:

A1: Date of birth; A2: the person’s birth date.
B1: As-of date; B2: the date on which you want to know the age.
C1: Age in completed years; C2: the age formula.

Enter dates as real Excel dates, not text that merely looks like a date. Excel stores dates as serial numbers and displays them according to the cell’s number format. If a pasted date is treated as text, functions may return an error or an unexpected result. To check, select the cell and change its format to a date or general number; a real date will display as a date or a serial value.

For a fixed historical or reporting date, type the date into B2 and keep it there. This makes the result reproducible later: the age for the same birth date and same as-of date will remain the same. Be mindful of ambiguous entries such as 03/04/2026, which can mean March 4 or April 3 depending on regional settings. Use an unambiguous date entry method or confirm the displayed date after entering it.

Calculate age as of today

If the desired reference point is the current date rather than a date stored in a cell, use Excel’s TODAY function as the second argument. With the date of birth in A2, enter this formula in the cell where you want the current age:

Assuming A2 contains the date of birth and you want completed years as of today: =DATEDIF(A2,TODAY(),"Y")

TODAY() returns the current date based on the computer or service date when Excel recalculates the workbook. It is not a live clock that updates continuously at midnight; the value updates when recalculation occurs, such as when the workbook is opened or recalculated. That behavior is convenient for a live age list, but it is not suitable when you need a permanent record of someone’s age on a past date. For that, use a date entered in B2 instead.

To force a recalculation in Excel for Windows, press F9. On many Mac keyboards, use Fn+F9 if the function keys control hardware features; the exact key behavior depends on macOS and keyboard settings. Excel for the web recalculates formulas as part of its normal workbook calculation behavior.

Show years, months, and days

Sometimes a completed-years number is not detailed enough. DATEDIF can also return the number of remaining whole months after completed years, and a day component. With A2 holding June 15, 1990, and B2 holding November 2, 2026, use this formula to display all three parts in one cell:

Assuming A2 is the birth date and B2 is the as-of date: =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"

For those example dates, the result is “36 years, 4 months, 18 days”: June 15 to October 15 is four full months, and October 15 to November 2 is 18 days. The ampersands join the numeric results with the text labels. The formula returns text, not a number, so it is best for display rather than later arithmetic.

Use the month and day components with care. Calendar months have different lengths, and Excel’s DATEDIF documentation warns that the "MD" unit can produce an incorrect result in some situations. If the breakdown is needed for a contract, payroll rule, medical record, or another consequential purpose, define the exact counting convention and verify edge cases such as month-end and February dates. For ordinary reporting, completed years are usually the clearest and least ambiguous age measure.

Use a readable result and fill the formula down

For a small list, enter the formula in C2 and copy it down for each person. If the references are relative, Excel changes A2 and B2 to A3 and B3 in the next row, as needed. You can fill down by dragging the fill handle at the lower-right corner of the selected cell, or copy and paste the formula into the cells below.

If you prefer a label such as “36 years” rather than a bare number, join the completed-years result to text. With A2 as the birth date and B2 as the as-of date, use =DATEDIF(A2,B2,"Y")&" years". For the June 15, 1990 and November 2, 2026 example, the displayed result is “36 years.” Since this also returns text, retain a separate numeric age column if you plan to sort or calculate with the age.

When maintaining a longer register, consider using clear column headings and a consistent date format. If the data is converted to an Excel table, calculated-column formulas can be applied to new rows automatically. The exact table behavior depends on how the data is set up, but keeping the birth date and reference date in their own columns makes the calculation easier to inspect than embedding dates inside a long formula.

Common errors and edge cases

The birthday has not occurred yet. This is the main reason year subtraction gives a wrong age. DATEDIF with "Y" counts completed years rather than merely subtracting the year numbers.
The as-of date is earlier than the birth date. DATEDIF expects the start date to be earlier than or equal to the end date. Reversed dates can produce a #NUM! error. Check that A2 is the birth date and B2 is the later date.
A date is stored as text. Re-enter or convert the value to a real Excel date, then confirm the cell displays as expected. Importing data from another system is a common source of text dates.
Blank cells create confusing results. A blank birth date can be interpreted like a zero date in some calculations. For a clean report, check that every row has a valid birth date and as-of date before calculating.
February 29 birthdays need a defined convention. Calendar rules and organizational policies can treat the age change for a leap-day birth differently in a non-leap year. Excel cannot decide which policy applies. If an official age determination matters, follow the relevant rule and validate the result for those dates.

For a list containing incomplete records, a separate validation column or a clear data-entry rule is often better than disguising missing information as an age. That way, an empty or invalid date is corrected at its source rather than appearing as a plausible-looking number.

Choosing between a fixed date and TODAY

Use a fixed date in a cell when the question is “How old was this person on a particular day?” Examples include a year-end report, an eligibility cutoff, or a historical analysis. Keeping the date visible makes the calculation understandable to someone reviewing the workbook, and it prevents the answer from changing simply because time has passed.

Use TODAY() when the question is “How old is this person today?” Examples include a contact list or a worksheet that should show current ages whenever it recalculates. A formula tied to today is convenient, but it makes past results change over time. If you need to preserve an age at a point in time, record the as-of date and, where appropriate, save the resulting value or a dated copy of the workbook.

For most spreadsheets, DATEDIF with the "Y" unit is preferable to dividing the number of days between two dates by 365 or 365.25. A year is a calendar period with leap years, not always a fixed number of days, and fractional-year calculations can produce decimal ages that do not match the everyday meaning of completed age. Choose a different calculation only when a specific business rule calls for elapsed days or a fractional year.

Frequently Asked Questions

What is the Excel formula for age on a specific date?
Put the birth date in A2 and the as-of date in B2, then use =DATEDIF(A2,B2,"Y"). It returns completed years as of the date in B2.

How do I calculate age in Excel from date of birth to today?
With the birth date in A2, use =DATEDIF(A2,TODAY(),"Y"). The result changes when the workbook recalculates because TODAY returns the current date.

Why does subtracting the birth year give the wrong age?
Subtracting year numbers ignores whether the birthday has happened yet in the as-of year. DATEDIF with "Y" accounts for that and returns only full years completed.

Can I calculate age in months or days?
Yes. DATEDIF supports units such as "M" for whole months and "D" for total days between dates. For a years-months-days display, "Y", "YM", and "MD" can be combined, but verify the day component for edge cases because the MD unit has known limitations.

Why is DATEDIF missing from Excel’s function suggestions?
It is an older function that Excel still supports but may not suggest in autocomplete. Type the formula directly and check that the start date is not later than the end date.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.