To calculate someone’s age in completed years in Excel, use =DATEDIF(A2,TODAY(),"Y"), with the date of birth in A2. For a fixed or historical calculation date, replace TODAY() with a cell containing that date; DATEDIF returns the number of full years between the two dates.
What DATEDIF does
DATEDIF calculates the difference between a start date and an end date in a unit you choose. For age, the useful starting point is usually the person’s date of birth, and the end date is either today or a date you specify. The “Y” unit counts complete years, so someone who has not yet reached their birthday this year is not given credit for the next year.
This is different from subtracting birth year from current year. For example, a person born on December 20, 2000, is still 25 on December 19, 2026, even though 2026 minus 2000 equals 26. DATEDIF accounts for the month and day, not just the year numbers.
DATEDIF is supported in Excel for Microsoft 365 on Windows, although it may not appear in the function autocomplete list as you type. Enter the function directly in a cell. Its syntax is DATEDIF(start_date,end_date,unit): the start date must come first, the end date second, and the unit is entered in quotation marks.
Set up the birth date and calculation date
For a reusable worksheet, put each date in its own cell. A straightforward layout is a date of birth in A2 and a calculation date in B2. Enter genuine Excel dates rather than text that merely looks like a date. You can type a date such as 6/15/1990 in a cell, depending on your regional date settings, or create a date with Excel’s DATE function. Format the cell as a date if Excel displays a serial number.
Keeping the calculation date in a cell is especially useful for reports. It makes the age calculation auditable and lets you change the as-of date without editing the formula. For example, a membership list might calculate ages as of the first day of the year, while an archived report might need to show ages as of its publication date.
- Use one column for names, one for dates of birth, and one for the calculation date or result.
- Check that Excel recognizes both date entries as dates. A left-aligned entry can be a clue that a value is stored as text, although cell alignment can be changed.
- Use a consistent date format, such as yyyy-mm-dd, when people will read or enter dates from different regions.
Calculate completed age in years
With the date of birth in A2 and the as-of date in B2, enter this formula in the age result cell:
With a date of birth in A2 and an as-of date in B2, the formula is =DATEDIF(A2,B2,"Y"). For a birth date of June 15, 1990, and an as-of date of November 3, 2026, it returns 36, because 36 birthdays have passed by that date.
To calculate age as of the day the workbook recalculates, use TODAY instead of B2. With the date of birth in A2, the formula is =DATEDIF(A2,TODAY(),"Y"). TODAY updates to the current date when Excel recalculates the workbook, so the displayed age can change when a birthday passes. It is a date-only function; it does not calculate age in hours or account for the time of day.
If you want to verify a result, compare the birthday with the as-of date. For someone born on November 4, 1990, the age on November 3, 2026, is 35; on November 4, 2026, it becomes 36. DATEDIF handles that birthday boundary automatically.
Show years and months, or years, months, and days
Some worksheets need more detail than a whole-number age. The “YM” unit returns the number of remaining whole months after complete years are counted. With the date of birth in A2 and the as-of date in B2, use =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months". For June 15, 1990, through November 3, 2026, the result is “36 years, 4 months.” The ampersand joins the calculated values with text labels.
To include the remaining days, calculate the date reached after the completed years and months, then subtract it from the as-of date. With the same A2 and B2 layout, use =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&(B2-EDATE(A2,DATEDIF(A2,B2,"Y")*12+DATEDIF(A2,B2,"YM")))&" days". For the example dates above, it returns “36 years, 4 months, 19 days.” EDATE moves the birth date forward by the number of completed months represented by the years and remaining months.
These more detailed displays are often useful for elapsed-time records, but they are not always the right way to present a person’s age. For ordinary forms, whole completed years are clearer. Also, month-and-day decomposition depends on how calendar months and leap-day anniversaries are treated, so check the policy required for your application before relying on it for an official record.
Choose between TODAY and a fixed as-of date
Use TODAY when the result should represent the person’s age now and update over time. Because TODAY is a volatile function, Excel can recalculate it as workbook calculations occur. This is convenient for a live roster, but it means an old report containing the formula can show a different age later unless its calculation date is preserved.
For historical or repeatable results, store the as-of date in a cell and refer to it. With the birth date in A2 and the fixed date in B2, use =DATEDIF(A2,B2,"Y"). Save the date used with the report, rather than relying on the day someone happens to open the file. A manually typed date or a date imported with the source data can serve as that fixed reference.
If you need to keep a snapshot, you can copy the calculated results and use Paste Special > Values to replace formulas with their current values. Keep a copy of the original workbook first if you may need the live formulas later. On Mac, the same Paste Special > Values option is available in Excel; ribbon and shortcut details can vary with the version and keyboard settings.
DATEDIF units and common problems
DATEDIF accepts several units. The units are written as text in quotation marks, and the capitalization shown below is conventional; Excel does not require uppercase letters.
"Y"counts complete years, the usual choice for age."M"counts complete months across the entire date interval."D"counts days between the dates."YM"counts remaining months after complete years."YD"counts days after disregarding the year difference."MD"counts days after disregarding the month and year differences; use it cautiously because it can produce problematic results for some date combinations.
Common errors usually come from the input dates or their order. If the start date is later than the end date, DATEDIF returns #NUM!. A text value that Excel cannot interpret as a date can lead to an error or an unexpected result. Regional settings also matter: 03/04/2001 can be interpreted as March 4 or April 3, so an unambiguous date format or a date cell selected from a controlled source is safer.
To leave the result blank when a birth date or calculation date is missing, and flag a birth date later than the as-of date, use this guarded version. With the date of birth 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 cell is blank, “Check dates” if the birth date is after the calculation date, and otherwise the completed age. The source cells still need to contain valid dates.
People born on February 29 can raise a separate policy question: should their birthday in a non-leap year be treated as February 28 or March 1? Rules and expectations can depend on context. If the exact boundary matters, test the formula against the convention your organization uses rather than assuming every system defines the anniversary identically.
DATEDIF compared with other age methods
DATEDIF is usually the simplest choice when the required answer is the number of complete years between two dates. A formula that subtracts YEAR values is shorter, but it can be one year too high before the person’s birthday. YEARFRAC can express an interval as a fraction of a year, but the fractional result depends on the selected day-count basis and does not directly express the common “age in completed birthdays” convention.
Manual formulas that compare the month and day can also calculate completed age, but they are easier to get wrong around date boundaries and less readable for many spreadsheet users. DATEDIF makes the intended rule explicit: count complete years between a birth date and an end date. For a basic age column, prefer the “Y” unit; add other units only when the worksheet genuinely needs them.
For Excel for the web, the same DATEDIF formula is generally available in a worksheet. Excel for Mac also supports it; the formula syntax is the same, although menus and keyboard shortcuts can differ from Windows. If a workbook is opened in another spreadsheet program, confirm that program supports DATEDIF and check its behavior before sharing results as authoritative.
Frequently Asked Questions
What is the Excel formula for age from date of birth?
With a birth date in A2, use =DATEDIF(A2,TODAY(),"Y") to return completed years as of today.
How do I calculate age on a specific date?
Put the birth date in A2 and the desired as-of date in B2, then use =DATEDIF(A2,B2,"Y"). This is useful for historical reports and fixed evaluation dates.
Why does DATEDIF not appear in Excel’s formula suggestions?
DATEDIF may not show in autocomplete, but it is supported in Excel. Type the function and its arguments directly, checking the commas, quotation marks, and parentheses.
Why does DATEDIF return #NUM!?
The most common reason is that the start date is later than the end date. Check the cell values and argument order: date of birth first, calculation date second.
Can Excel show age in years and months?
Yes. Combine DATEDIF with the “Y” and “YM” units, as in the example above. The result is a text label such as “36 years, 4 months,” rather than a number suitable for further arithmetic.
Does the age formula update automatically?
A formula using TODAY can update when Excel recalculates. A formula that refers to a date entered in a cell stays tied to that date until the cell value changes.
Related
- Do Excel Named Ranges Automatically Expand?
- Excel Merge Vertical Cells: Steps, Limits, and Alternatives
- Excel Merge Multiple Cells Into One Cell: A Clear Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Growth Analysis: Formulas, Trends, and ExamplesExcel growth analysis measures how a value changes over time. For two consecutive values in B2 and B3,…
- Clear Excel Spreadsheet but Keep Formulas in PlaceTo clear entered values in Excel while keeping formulas, select the cells to clean, choose Home > Find…
- Excel Next Number in Sequence: Formulas and TipsWith the first number in A2, enter =A2+1 in A3 to calculate the next number in a sequence…
- Excel Formula to Reference a Sheet Name from a CellTo reference a worksheet named in a cell, use Excel’s INDIRECT function. If A1 contains the sheet name…
- XLOOKUP Examples: Exact, Approximate, and Wildcard MatchXLOOKUP’s match_mode argument controls whether Excel finds an exact value, a nearby value, or a wildcard pattern. For…
- Excel Time Sheets for Employees: Setup, Formulas and TipsYes. Excel can track employee start times, finish times, unpaid breaks, and hours worked in a reusable time…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.