Calculate Age in Excel with DATEDIF: Formula and Tips

Formulas & functions · Updated November 19, 2026

To calculate someone’s age in completed years in Excel, enter their birth date in A2 and use =DATEDIF(A2,TODAY(),"Y"). DATEDIF compares the start date with the end date and returns the number of full years that have elapsed; for a fixed reference date, replace TODAY() with a cell containing that date.

What DATEDIF Calculates

DATEDIF is a date function that returns the difference between two dates in a specified unit. Its syntax is DATEDIF(start_date,end_date,unit). The start date must be earlier than or equal to the end date. For an age calculation, the start date is usually the date of birth and the end date is today or another chosen date.

The function counts completed intervals, not simply the difference between the year numbers. For example, a person born on 15 June 1990 is 36 on 3 November 2026: their 36th birthday has passed, but their 37th has not. Subtracting the birth year from the current year would return 36 in this case, but it can be one year too high when the birthday has not yet occurred.

DATEDIF is an older Excel function retained for compatibility. It works in Microsoft 365 for Windows, as well as Excel for Mac and Excel for the web, but it may not appear in the formula autocomplete list as you type. You can still enter it directly in a cell.

Calculate Completed Years

For a live age that updates as the calendar advances, put a valid birth date in A2. The formula below uses the computer’s current date:

With the birth date in A2, enter this in another cell: =DATEDIF(A2,TODAY(),"Y"). For a birth date of 15 June 1990 and a current date of 3 November 2026, it returns 36.

The unit argument “Y” means complete years. Excel does not round the age up because someone is close to their next birthday; the result changes on the birthday itself according to Excel’s date calculation. Format the result as General or Number. Applying a date format to the result can make the number appear as an unrelated date.

To set this up:

  1. Enter each birth date as a real Excel date, not as text.
  2. Place the formula in the age column for the first record.
  3. Use the fill handle to copy the formula down for other rows; relative references adjust from A2 to A3, A4, and so on.
  4. Check a few known birthdays, especially dates near the end of a month or year.

Use a Fixed As-Of Date

TODAY() is useful for a current-age list because it updates when Excel recalculates the workbook. It is not ideal when you need to reproduce an age as of a particular day, such as the date of a report. A fixed reference date makes the result repeatable and easier to audit.

Assume A2 contains the birth date and B2 contains the as-of date. With 15 June 1990 in A2 and 3 November 2026 in B2, enter =DATEDIF(A2,B2,"Y") in C2. The result is 36. To calculate ages for several people on the same reporting date, put that date in B2 and use an absolute reference when copying the formula down: =DATEDIF(A2,$B$2,"Y").

In a real worksheet, enter the reference date as a date value, for example by typing a date in a format Excel recognizes for your regional settings. You can format it through Home > Number Format > Short Date. A displayed date can look different under different regional settings while still representing the same stored date.

Show Years, Months, and Days

For a more descriptive result, DATEDIF can return completed years, remaining months, and remaining days. Assume A2 contains 15 June 1990 and B2 contains 3 November 2026. In C2, enter =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days". It displays 36 years, 4 months, 19 days.

The units used in this example have different meanings:

  • “Y” returns complete years between the dates.
  • “YM” returns remaining months after complete years are excluded.
  • “MD” returns remaining days after complete months and years are excluded.
  • “M” returns complete months overall, while “D” returns the total number of days.

The combined result is text, not a number, so it is convenient for display but unsuitable for calculations that expect a numeric age. Keep the individual year, month, and day values in separate cells if you need to sort or calculate with them. Also be cautious with “MD”: Microsoft documents known limitations with this unit and does not recommend relying on it in every date combination. For a report where exact day-level age matters, verify edge cases or calculate the day component with a method designed for the required calendar convention.

Make the Formula Safer for Blank or Future Dates

A blank birth-date cell can lead to a misleading result because Excel treats dates as serial numbers and DATEDIF still attempts a calculation. A birth date later than the as-of date causes a #NUM! error. If the worksheet is shared or contains incomplete records, return a blank until both dates are present and show a message for reversed dates.

With the birth date in A2 and the as-of date in B2, enter =IF(OR(A2="",B2=""),"",IF(A2>B2,"Check dates",DATEDIF(A2,B2,"Y"))). It returns a blank if either input is empty, the text “Check dates” if the birth date is after the reference date, and otherwise the completed age in years. Format the result column as General so the message and age display appropriately.

This check assumes A2 and B2 contain valid dates. Dates imported from another system may be text even if they look like dates. If a cell will not behave as a date, check its alignment and number format, then convert the source data rather than relying on a display format alone. Changing a text cell’s format to Date does not necessarily convert the underlying text into a date value.

Common Errors and Date Edge Cases

  • #NUM!: The start date is later than the end date, or an input is not a usable date. Check the order of the arguments and the source values.
  • Unexpectedly high age: A formula based on YEAR(TODAY()) minus YEAR(birth date) ignores whether the birthday has passed. DATEDIF with “Y” handles completed years directly.
  • Wrong result after copying: A reference to the fixed as-of date may have shifted. Use dollar signs, such as $B$2, to keep that cell fixed when filling down.
  • Incorrect date interpretation: A text entry such as 04/05/2000 can mean April 5 or May 4 depending on regional settings. Use unambiguous date entry or import dates with the correct locale.

Leap-day birthdays deserve special care. A person born on 29 February has no 29 February birthday in a non-leap year. Excel’s date functions apply their own calendar calculations, but organizations and jurisdictions may define whether an age milestone is treated as occurring on 28 February or 1 March. If that distinction affects eligibility, compliance, or another formal decision, confirm the applicable rule and test the workbook against it rather than assuming every convention is identical.

Also avoid including a time of day in a birth-date cell unless it is genuinely needed. Excel stores dates and times as numbers, with the time represented as a fraction of a day. For ordinary age-in-years calculations, use date-only values so that hidden time components do not complicate checks or comparisons.

DATEDIF Compared with Other Approaches

For a person’s age in full years, DATEDIF is usually clearer than building a birthday test from separate YEAR, MONTH, and DAY calculations. It expresses the intended calculation directly and avoids the common error of subtracting year numbers alone.

A formula based on YEAR(TODAY())-YEAR(A2) can be useful as a rough estimate, but it is not a correct completed age in every case. If the current date is 3 November 2026 and the birth date is 20 December 1990, that subtraction gives 36, although the person is still 35. The DATEDIF formula returns 35 until the birthday has passed.

DATEDIF is not the only option for broader date analysis. YEARFRAC can express a difference as a fraction of a year, but the answer depends on its day-count basis and may not match the everyday meaning of age in completed birthdays. For a conventional age in years, use DATEDIF with “Y”; choose another method only when the task specifically requires a fractional year or a different calendar rule.

Frequently Asked Questions

Does DATEDIF update automatically?
When the end date is TODAY(), the displayed age can update when Excel recalculates the workbook on a later date. TODAY() is a volatile function, so it may recalculate during normal workbook recalculation. A date typed directly into a cell stays fixed until you change it.

Why can’t I find DATEDIF in Excel’s function list?
DATEDIF is retained for compatibility with older spreadsheet files, and it may not be suggested by formula autocomplete. Type the function and its arguments directly. Check that the start date is not later than the end date if Excel returns an error.

How do I calculate age in months instead of years?
With the birth date in A2 and the reference date in B2, use =DATEDIF(A2,B2,"M") for the total number of complete months. This is different from “YM”, which returns only the leftover months after full years have been removed.

Can I calculate age at a past date?
Yes. Put the birth date in A2 and the chosen historical date in B2, then use =DATEDIF(A2,B2,"Y"). The result is the completed age on that reference date, provided both cells contain valid dates and the birth date is not later than B2.

Can I use the formula in Excel for Mac or the web?
Yes. DATEDIF is available in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. The formula syntax is the same; date display and accepted typed date formats can depend on the workbook’s regional settings.

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.