Excel Formula for Age From Date of Birth

The simplest Excel formula for age from a date of birth is =DATEDIF(A2,TODAY(),"Y"), assuming the birth date is in cell A2. It returns completed years as of today; use a reference to another cell instead of TODAY() when you need age on a specific date.

What the age formula calculates

DATEDIF calculates the time between two dates. Its first argument is the earlier date, its second argument is the later date, and its third argument specifies the unit to return. The unit "Y" means complete years, which is the usual meaning of a person’s age in years.

For example, with a birth date of 15 June 1990 in A2, the formula returns 36 when evaluated on 2 November 2026. The person has already had their birthday that year. If the same person’s birthday were 15 December, the result on 2 November 2026 would still be 35: Excel does not count the next year until the birthday has passed.

This is different from dividing the number of days since birth by 365.25 or subtracting birth year from current year. Neither shortcut checks whether the birthday has occurred yet, and years do not all have the same number of days. For a conventional age in completed years, use DATEDIF.

Enter the formula in Excel

Enter each person’s birth date in a cell, such as A2. Use a genuine Excel date, not text that only looks like a date.
Select the cell where you want the age to appear, such as B2.
With the birth date in A2 and the age result in B2, enter =DATEDIF(A2,TODAY(),"Y") in B2 and press Enter.
To calculate ages for more rows, copy or fill the formula down. Excel adjusts A2 to A3, A4, and so on.

The formula works in Excel for Microsoft 365 on Windows, and the same formula can be used in Excel for Mac and Excel for the web. In normal US-style formula syntax, arguments are separated by commas. Some regional settings use semicolons as separators; if Excel rejects a comma-separated formula, use the separator required by your Excel settings.

DATEDIF is a supported legacy worksheet function, but Excel may not display it in the function autocomplete list while you type. You can still enter it directly. Keep the quotation marks around "Y" straight double quotes, and make sure the closing parenthesis is present.

Calculate age on a chosen date

An age that updates every day is useful for a live roster, but a report, form, or historical record may need age on a fixed date. Put the birth date in A2 and the date to calculate age on in B2. Then, with those cells as the layout, enter =DATEDIF(A2,B2,"Y") in C2. If A2 contains 15 June 1990 and B2 contains 2 November 2026, C2 returns 36.

Using a date in a cell makes the calculation easier to audit and reuse. For example, a team can place a report’s “as of” date in B1 and refer to that same cell in each row. With birth dates in A2:A100 and the shared as-of date in B1, enter =DATEDIF(A2,$B$1,"Y") in C2, then fill down. The dollar signs keep the reference fixed at B1 as the formula is copied. This arrangement also lets you change the report date once rather than editing every formula.

To type a fixed date directly into a formula, Excel’s DATE function avoids ambiguity between regional date formats. With a birth date in A2, enter =DATEDIF(A2,DATE(2026,11,2),"Y") to calculate age as of 2 November 2026. The arguments to DATE are year, month, and day. A cell reference is usually easier to maintain than a date embedded in the formula.

Show years and remaining months

Sometimes a whole-number age is not detailed enough. DATEDIF can return the complete years and the months left after those years. With a birth date in A2 and age measured as of today, enter =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months". The "YM" unit reports remaining months after subtracting complete years. For a person who is 36 years and four months old, the displayed result is “36 years, 4 months.”

This combined expression returns text because it joins numbers with words. That is convenient for a label, but it is not ideal if a later calculation needs the age as a number. Keep the completed-years formula in its own numeric cell when you expect to sort, filter, compare, or calculate with ages; build a separate display formula only when needed.

If you need years, months, and days, decide what “days” should mean for your use case before building a display. The DATEDIF unit "MD" is sometimes used for leftover days, but Microsoft warns that this unit can return an inaccurate result in some scenarios. For records where an exact interval matters, check the date arithmetic against known cases or use a clearly defined anniversary-based calculation rather than assuming every interval unit behaves identically.

Keep the result current or preserve a snapshot

TODAY() returns the current date from the computer or calculation environment and updates when Excel recalculates the workbook. An age based on TODAY() can therefore change when a birthday passes. This is normally appropriate for a live list, but it means a workbook opened next year may show a different age from the one shown when the file was first saved.

For a dated report, use a fixed as-of date in a cell instead. You can type the date into a designated cell and use an absolute reference in the age formulas. If you need to preserve the displayed ages as a historical snapshot, copy the calculated results and use Paste Special > Values in a separate snapshot column or workbook. This replaces formulas with their current values, so those entries will not update automatically.

Excel calculation settings can affect when formulas refresh. In Microsoft 365 for Windows, check Formulas > Calculation Options and select Automatic for ordinary day-to-day recalculation. Excel for Mac has calculation options on the Formulas tab as well. If a workbook is set to Manual calculation, a formula using TODAY() may not refresh until recalculation occurs.

Check date entries and prevent common errors

If a formula returns #VALUE! or an unexpected answer, check the underlying date first. A cell may contain text such as an imported date string rather than a numeric Excel date. Changing the cell’s number format to Date does not necessarily convert text into a real date; it may only change how the value is displayed. Re-enter the date in a recognized format or convert the imported data before relying on the age calculation.

Confirm the order of dates. The birth date must be earlier than the end date. A future birth date can make DATEDIF return #NUM!.
Check the year. Dates such as 04/05/2000 can mean different days depending on regional settings. A clear entry such as 5 April 2000, or a date created with DATE, reduces ambiguity.
Do not treat zero as an error. Someone younger than one year has an age of 0 when the result is expressed in completed years.
Handle blank rows intentionally. A blank birth-date cell is not a valid person’s birth date. If your data includes empty rows, decide whether the age cell should remain empty rather than showing an error or a number.

With birth dates in A2:A100, enter =IF(A2="","",DATEDIF(A2,TODAY(),"Y")) in B2 and fill down to leave the result blank when A2 is empty. This formula checks for an empty input before calculating. It does not validate every possible bad entry, so a future date or text value may still need to be corrected at the source.

Leap-day birthdays and age conventions

People born on 29 February have a birthday date that does not appear in most years. The age calculation still returns a whole number, but different contexts can define the effective birthday in a non-leap year differently. An organization, official form, or jurisdiction may specify whether the relevant date is 28 February or 1 March. Do not assume that a spreadsheet formula settles a legal or policy question; follow the rule that applies to the record you are preparing.

For ordinary personal tracking, the standard completed-years calculation is often sufficient. For eligibility, benefits, age-restricted access, or other consequential decisions, confirm the required convention and test the formula around the relevant date. Include a leap-day example in your checks if your data contains such birthdays.

Why not use YEARFRAC or subtract years?

YEARFRAC returns a fraction of a year between dates, not necessarily a person’s completed age. Its result depends on the chosen day-count basis, and rounding that fraction can produce an age that changes before or after the actual birthday. It can be useful for measuring portions of a year in other calculations, but it is not the clearest choice for a whole-years age.

Similarly, a formula that subtracts the birth year from the current year ignores month and day. With birth dates in A2, =YEAR(TODAY())-YEAR(A2) gives a person born in December an age one year too high for much of the year. Use DATEDIF with the "Y" unit when the desired answer is the number of birthdays already passed.

Frequently Asked Questions

What is the formula for age in Excel?
With the date of birth in A2, use =DATEDIF(A2,TODAY(),"Y") for completed years as of today. Replace TODAY() with a reference to a cell containing another end date if required.

How do I calculate age from date of birth in Excel without DATEDIF?
DATEDIF is the most direct option for completed years. Subtracting birth years or dividing elapsed days by 365 does not reliably account for whether the birthday has occurred, so those shortcuts can give the wrong age.

How do I calculate age on a specific date?
Put the birth date in A2 and the chosen date in B2, then use =DATEDIF(A2,B2,"Y"). The result is age in completed years on the date in B2.

Why does DATEDIF not appear in Excel’s formula suggestions?
Excel may omit this legacy function from autocomplete, but you can type it directly into a cell. Check the spelling, quotation marks around the unit, and commas or regional argument separators if Excel reports a formula error.

How do I stop Excel from showing an error for blank birth dates?
With a birth date in A2, use =IF(A2="","",DATEDIF(A2,TODAY(),"Y")). It leaves the result cell blank when A2 is empty, while still calculating an age for a valid date.

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.