To calculate age in completed years from a date of birth in Excel, enter the birth date in A2 and use =DATEDIF(A2,TODAY(),"Y"). The formula compares the date in A2 with today and counts full years; for a result tied to a chosen date instead, replace TODAY() with a cell containing that date.
How the DATEDIF age formula works
DATEDIF calculates the interval between two dates. Its arguments are the start date, the end date, and a text unit that tells Excel which part of the interval to return. For age, the start date is the date of birth, the end date is the date on which you want to measure age, and the unit “Y” means complete years.
With the birth date in A2 and the current date supplied by Excel, use =DATEDIF(A2,TODAY(),"Y"). For example, if A2 contains August 15, 2000, and the date is November 3, 2026, the result is 26. Before August 15, 2026, the result would still be 25: DATEDIF does not round up based on the number of days elapsed in the current year.
DATEDIF is an older Excel function that remains available for compatibility with Lotus 1-2-3. It may not appear in Excel’s formula autocomplete or function suggestions, but you can type it into a cell yourself. In Microsoft 365 for Windows, Mac, and Excel for the web, the basic formula works the same way.
Use a fixed date for reproducible ages
TODAY() updates to the current date when Excel recalculates the workbook. That is useful for a live roster or report, but it means the displayed ages will change over time. If you need to reproduce a report later, calculate ages as of a date stored in a cell instead.
For a fixed example, put each date of birth in A2:A20 and enter November 3, 2026, as an Excel date in B1. With a birth date in A2 and the as-of date in B1, use =DATEDIF(A2,$B$1,"Y"). The dollar signs keep the reference to B1 fixed when you fill the formula down. With A2 set to August 15, 2000, and B1 set to November 3, 2026, the result is 26.
To make B1 reflect the current date automatically, put a formula there instead. With B1 designated as the as-of-date cell, use =TODAY(). The age formula can then continue to refer to B1, making it easy to switch between today’s date and a reporting date you enter manually. If you replace TODAY() with a typed date, enter it as a real Excel date rather than text.
Calculate age for a whole list
For a list of people, put names in column A and dates of birth in column B, with the first record in row 2. If the as-of date is in E1, enter this formula in C2, then fill it down: =DATEDIF(B2,$E$1,"Y"). For instance, a birth date of August 15, 2000, and an as-of date of November 3, 2026, return 26. Each copied formula uses the birth date from its own row while continuing to use E1.
In Microsoft 365, you can use the fill handle at the lower-right corner of C2 to copy the formula down. Double-clicking the fill handle can also fill it alongside adjacent data. On Windows, Ctrl+D fills formulas down after you select the formula cell and the cells below it; on Mac, use Command+D. The function itself does not change between Windows and Mac.
If the workbook is a recurring report, keeping the as-of date in one clearly labeled cell is usually easier to audit than embedding dates in many formulas. Label E1, for example, “Age calculated as of,” and format it as a date. A reviewer can then see exactly which date was used and update all the results by changing one cell.
Show years and remaining months
Sometimes a whole-number age is not detailed enough. DATEDIF’s “YM” unit returns the number of whole months left after complete years have been counted. This can express an age such as 26 years and 2 months without attempting to show a potentially confusing decimal age.
With the date of birth in A2 and the as-of date in B1, use =DATEDIF(A2,B1,"Y")&" years, "&DATEDIF(A2,B1,"YM")&" months". For August 15, 2000, through November 3, 2026, the displayed result is “26 years, 2 months.” The ampersands join the calculated numbers with text; because the output is text, it is intended for display rather than further arithmetic.
DATEDIF also accepts “D” to return the number of days between two dates. That is a day count, not an age in years, and it will vary with leap years. Avoid treating a decimal such as 26.2 as a precise age: decimal-year calculations depend on the method and denominator used, and they do not communicate completed birthdays as clearly as years and months.
Handle blank dates and invalid records
A blank birth-date cell can lead to a misleading result because Excel may treat a blank reference like a zero date in date calculations. A future date of birth also makes the end date earlier than the start date, which can cause DATEDIF to return the #NUM! error. A guard formula can leave incomplete rows empty and flag future dates.
Assuming A2 contains a date of birth and B1 contains the as-of date, use =IF(OR(A2="",B1=""),"",IF(A2>B1,"Check dates",DATEDIF(A2,B1,"Y"))). The result is blank if either date is missing, “Check dates” if the birth date is after the as-of date, and otherwise the completed age in years. This assumes entries in the date cells are valid Excel dates; it does not repair dates stored as text.
If a formula returns an error unexpectedly, check the underlying values. A date may look correct on screen while actually being text imported from another system. A quick check is to select the cell and inspect its value or try changing its number format; a true date will normally display differently when given a date format, while text often will not. Regional settings can also affect how ambiguous entries such as 04/05/2000 are interpreted, so an unambiguous date entry or a consistent import format helps.
For a data-import problem, convert the text to actual dates before calculating age. Depending on the source and format, Excel’s Text to Columns feature can help: select the affected column, choose Data > Text to Columns, follow the prompts, and select the appropriate date order where offered. Check a few converted values before applying the age formula to a large dataset.
Leap-day birthdays and age conventions
A person born on February 29 has a birthday date that does not occur in every calendar year. DATEDIF still returns a count of completed years based on the two dates supplied, but an organization may need to decide how it treats the birthday in a non-leap year. Depending on the purpose and applicable rules, the chosen convention may treat the birthday as February 28 or March 1.
That distinction matters for decisions that depend on a precise legal or policy definition of age, not just for displaying a general age in a spreadsheet. Do not assume that a formula alone establishes the correct convention for every jurisdiction or use. Confirm the rule that applies, then select an as-of date and method consistent with it. For ordinary recordkeeping, document the convention if users might interpret an age around the birthday differently.
More generally, age is date-dependent. Someone’s age changes on their birthday, not at the start of a new calendar year. That is why subtracting birth years alone can be wrong: a person born late in the year has not yet had the birthday that would increase their age. DATEDIF with “Y” accounts for whether the birthday has passed relative to the end date.
DATEDIF compared with other approaches
For the age in completed years, DATEDIF is usually the clearest compact choice. It directly expresses “whole years between these dates,” and its “Y” unit avoids the birthday check that a basic subtraction of year numbers would require. The function’s limited visibility in autocomplete can make it seem unusual, but it is supported in current Excel.
A common shortcut is to subtract the birth year from the current year. That can overstate age by one when the birthday has not yet occurred in the current year. Another shortcut divides the number of elapsed days by 365 or 365.25 and rounds or truncates the result. Those methods can be inappropriate for completed age because calendar years are not all the same length, and the result may depend on the rounding choice. For reliable completed years, use DATEDIF rather than a rough day-to-year conversion.
If you need an age expressed as years, months, and days, take extra care before using DATEDIF’s “MD” unit for the day remainder. Excel’s documentation cautions that “MD” can produce an inaccurate result in some situations. If exact calendar components matter for a formal calculation, define the expected anniversary and month-end behavior explicitly and verify the method against representative dates, especially month ends and leap days. For a simple age in years, there is no need to use “MD”.
Frequently Asked Questions
What is the Excel formula for age from date of birth?
With the date of birth in A2, use =DATEDIF(A2,TODAY(),"Y") to return completed years as of today. Replace TODAY() with a cell reference if you want the age as of a particular date.
Why is DATEDIF not showing in Excel autocomplete?
DATEDIF is retained for compatibility and may not appear in autocomplete or the function list. Type the function and its arguments directly into the formula bar or cell; it is available in current Excel versions, including Microsoft 365.
How do I calculate age on a specific date?
Put the birth date in A2 and the date you want to calculate against in B1, then use =DATEDIF(A2,B1,"Y"). B1 must contain a real Excel date, and it must not be earlier than the date in A2.
Can I calculate age in years and months?
Yes. Use DATEDIF with “Y” for completed years and “YM” for the remaining whole months. Join those results with labels if you want a readable display; remember that a joined result is text, not a number for later calculations.
Why does my age formula return #NUM!?
DATEDIF returns #NUM! when its start date is later than its end date. Check that the birth date is not in the future and that the as-of date is correct. Also confirm that both cells contain valid dates rather than incorrectly imported text.
Does the formula work in Excel for Mac or the web?
Yes. The DATEDIF formula syntax is the same in Excel for Microsoft 365 on Windows, Mac, and the web. Some interface details differ, but you can enter the formula directly in a cell on each platform.
Related
- Excel: Merge Cell Data Into One Cell
- Excel Merge Cells Greyed Out? Causes and Fixes
- Excel: Make Rows Collapsible with Grouping and Outlines
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort Excel Workbook Tabs Alphabetically in ExcelExcel does not have a built-in command that automatically sorts workbook tabs alphabetically. In Microsoft Excel for Microsoft…
- SUMIF in Excel: Practical Examples and FormulasIn Excel for Microsoft 365, SUMIF adds values that meet one condition. Its syntax is =SUMIF(range, criteria, [sum_range]);…
- Average Value in Excel: Formula, Examples, and TipsTo calculate an average value in Excel, use the AVERAGE function: with the numbers 12, 15, 18, 21,…
- 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…
- Excel INDIRECT: Link to Another Tab with a FormulaTo refer to a cell on another worksheet using a sheet name stored in A1, enter =INDIRECT("'"&A1&"'!B2"). If…
- XLOOKUP Excel Column Match: Find a Column by HeaderTo match a value in an Excel column and return a value from another column, use XLOOKUP with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.