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 birth date with today’s date and returns the number of full years; use an “as of” date in another cell instead of TODAY() when you need a fixed or historical age.
The Basic DATEDIF Age Formula
DATEDIF is useful for age calculations because it counts completed time between two dates rather than simply subtracting their year numbers. In Microsoft Excel for Microsoft 365 on Windows, enter a genuine Excel date in A2, then put the formula in another cell, such as B2. The result is a whole number: someone who has not yet reached their birthday this year is still counted at their previous age.
For example, with a birth date of August 17, 1990, and today set to November 3, 2026, the formula returns 36. If the birth date is November 20, 1990, it returns 35 on that same date, because the 2026 birthday has not happened yet. TODAY() updates as the date changes, so the displayed age can change automatically on a birthday.
DATEDIF works in Excel for Microsoft 365, including Excel for the web. Its syntax is =DATEDIF(start_date,end_date,unit): the first argument is the earlier date, the second is the later date, and the unit is a quoted text value specifying what to count. Excel’s function autocomplete may not offer DATEDIF while you type, but the function can still be entered directly.
Calculate Age on a Specific Date
Using TODAY() is convenient for a live age, but it is not appropriate for every report. You might need to know how old a customer was on a purchase date, how old an employee was on a particular review date, or how old someone will be on a future date. Put the birth date in A2 and the date to calculate against in B2. Then use =DATEDIF(A2,B2,"Y"). If A2 contains January 15, 2000, and B2 contains November 3, 2026, the result is 26.
This arrangement also makes the calculation easier to audit: the two dates are visible in worksheet cells rather than hidden inside a formula. To calculate several people, put their birth dates in A2:A20 and the corresponding as-of dates in B2:B20, enter the formula in C2 using A2 and B2, then fill it down. Check that each row’s dates are paired correctly before relying on the results.
For a fixed report date, enter that date in a cell rather than using TODAY(). A formula that uses TODAY() is volatile in the everyday sense that its answer changes when Excel recalculates on a later date; it does not preserve the age as it was when the report was first created. If you need an archived result, keep the as-of date and consider saving a copy of the workbook or preserving the calculated value under your normal recordkeeping process.
What the DATEDIF Units Mean
The unit determines what DATEDIF returns. The three most useful choices for age and elapsed time are:
"Y"returns complete years. This is the usual choice for age in years."M"returns complete months between the dates, not just the number of calendar-month boundaries crossed."D"returns the total number of days between the dates.
DATEDIF also accepts units that leave out part of the elapsed time. "YM" returns the remaining months after complete years are excluded, and "YD" returns the remaining days after complete years are excluded. "MD" returns the remaining days after complete months and years are excluded. These partial units can be useful for a years-and-months display, but they are not interchangeable with total months or total days.
For example, with a birth date of January 15, 2000, and an as-of date of November 3, 2026, a years-months-days display can use =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days". With those dates, it displays “26 years, 9 months, 19 days.” This combines three separate calculations: 26 full years, 9 additional full months, and 19 remaining days.
Use the combined display when a reader needs a human-readable duration, not when a numeric value is needed for sorting, averaging, or further calculations. The result is text because it includes words and separators. Also treat the MD unit with care: Microsoft notes that it can return an incorrect result in some situations. For age in completed years, use Y; if the exact day-level breakdown matters, verify the result for your date cases or calculate and present the components in a way suited to your rules.
Set Up Dates Correctly
DATEDIF needs dates Excel recognizes as dates, not merely strings that look like dates. Type dates using a format your regional settings understand, or use Excel’s date controls, and apply a date number format if the cells display as serial numbers. A cell may show a familiar date while containing text imported from another system; changing its number format alone does not convert text into a real date.
A quick check is to select the cell and inspect how Excel treats it. If a date is left-aligned by default, that can suggest it is text, although alignment can be customized and is not conclusive. Sorting or subtracting the values can also help reveal import problems. When dates come from a CSV or external system, confirm the day/month order: 04/05/2020 can mean April 5 or May 4, depending on the source and locale.
For a list, keep birth dates in one column and avoid mixing dates with notes such as “unknown” in the same range. If some dates are unavailable, leave them blank or handle them explicitly in a separate formula so the worksheet does not report misleading ages. A formula entered in B2 can be filled down for subsequent records, provided each row’s birth date is in column A.
Common Errors and How to Fix Them
A DATEDIF error usually points to an issue with the arguments or the underlying dates. Check these common causes before replacing the formula:
- #NUM! usually means the start date is later than the end date. For an age formula, verify that the birth date is earlier than the as-of date.
- #VALUE! can indicate that Excel cannot interpret one or both arguments as valid dates, or that a required argument has the wrong type.
- An unexpectedly large age can happen when the year and day/month have been interpreted in the wrong order, or when a birth date was entered in the wrong century.
- A formula displayed as text may have been entered into a cell formatted as Text, or may have a leading apostrophe. Change the cell format to General, then re-enter the formula.
- A result that does not update may be caused by a manually calculated workbook. Check the workbook’s calculation settings if formulas are not recalculating as expected.
When troubleshooting, inspect both referenced cells and confirm their order. DATEDIF does not automatically swap dates to make the calculation work. Also check for hidden spaces or text characters in imported values. A formula can look correct while one of its date cells contains text, an empty value, or a different date than the one shown in a report.
Why Subtracting Years Is Not Enough
A common shortcut is to subtract the birth year from the current year. That gives the right age only after the person’s birthday has occurred in the current year. Before the birthday, it overstates the age by one. For example, in 2026, subtracting 1990 from 2026 gives 36, but someone born on November 20, 1990, is still 35 on November 3, 2026. DATEDIF with the Y unit accounts for whether a full year has elapsed.
DATEDIF is also different from counting months or days. The Y unit returns an integer number of completed years; it does not return a decimal age such as 36.2. If you need a fraction of a year for a particular analysis, use a formula designed for fractional years and understand its day-count basis. A fractional-year calculation is not automatically the same thing as a conventional age in completed birthdays.
Leap-Day Birthdays and Date Conventions
Someone born on February 29 has a birthday date that does not appear in every calendar year. Excel can calculate an elapsed age from the dates, but the age a person or organization assigns in a non-leap year may depend on the rule being applied. Some contexts treat the anniversary as February 28; others use March 1. DATEDIF does not decide which legal, administrative, or organizational convention should apply.
If leap-day cases affect an eligibility decision, a contract, a policy, or an official record, establish the applicable convention first and verify the spreadsheet against it. Test dates immediately before and on the chosen anniversary. Do not assume that a formula’s output settles a legal or policy question; the worksheet is only applying a date calculation to the values and rules you supply.
Excel for Mac and Excel for the Web
The DATEDIF formula and the TODAY() function work in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. The formula examples here use commas between arguments, as specified for the usual English-language Excel setup. In some regional configurations, Excel expects semicolons as argument separators; use the separator required by your installation if a correctly written comma-separated formula is rejected.
The main differences across these platforms are in interface details, not in the age calculation itself. You can enter the formula directly into a cell in each version. In Excel for the web, changes are generally saved to the workbook stored online; on desktop Excel, saving behavior depends on where the workbook is stored and your settings. Neither difference changes what DATEDIF’s Y unit calculates.
Frequently Asked Questions
What is the Excel formula for age in years?
With the birth date in A2 and the current date as the endpoint, use =DATEDIF(A2,TODAY(),"Y"). It returns completed years, so it does not add a year until the birthday has passed.
How do I calculate age as of a date in another cell?
With the birth date in A2 and the as-of date in B2, use =DATEDIF(A2,B2,"Y"). This is useful for historical reports and for calculating age on a specific event date.
Can DATEDIF show age in years, months, and days?
Yes. Use the Y, YM, and MD units together to make a text display. The MD unit has known edge-case limitations, so check results carefully if the day component must be exact for all possible dates.
Why is DATEDIF missing from Excel’s function suggestions?
DATEDIF is an older compatibility function and may not appear in autocomplete or the function list. You can still type its formula directly into a cell.
Why does my age formula return #NUM!?
Check that the birth date is not later than the end date. DATEDIF expects the start date to be earlier than or equal to the end date; also confirm that both cells contain valid Excel dates.
Does DATEDIF calculate age in days?
Use the D unit to return the total number of days between two dates. That is a day count, not an age in years, and it can be a large number; use Y for the usual completed-years age.
Related
- Excel Not Responding and Not in Task Manager
- Excel Not Responding? How to Save Your Work
- Excel Not Responding? Recover an Unsaved File
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Combine Text From Two Cells with a SpaceWith text in A1 and B1, join the contents directly with =A1&B1; to put a space between them,…
- Excel Formulas With Text: How to Combine Words and ValuesTo use text in an Excel formula, put literal words inside straight double quotation marks, such as ="Ready".…
- Unique Excel List Creation: Formulas, Tools, and TipsTo create a unique list in Microsoft Excel for Microsoft 365 on Windows, enter =UNIQUE(A2:A100) in an empty…
- How to Compare Two Columns in Excel: Formulas and TipsTo compare values on the same row, put the lists in columns A and B, then enter a…
- XLOOKUP Partial Match: Wildcard Examples in ExcelFor a contains-style partial match, put the text to find in E2 and use =XLOOKUP("*"&E2&"*",A2:A7,B2:B7,"No match",2). The final…
- Excel Sum Unique Values: Formula Rules and ExamplesTo add each different numeric value once in Excel, put the values in B2:B8 and enter =SUM(UNIQUE(B2:B8)). The…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.