If the birth date is in cell A2, enter =DATEDIF(A2,TODAY(),"Y") to calculate age in completed years as of today. For example, if A2 contains June 15, 1990, the result on November 3, 2026, is 36.
How the age formula works
DATEDIF calculates the difference between two dates in a specified unit. In this formula, A2 is the start date, TODAY() supplies the current date, and “Y” tells Excel to return the number of complete years between them. That last detail matters: someone who is 36 years and 4 months old is shown as 36, not as a rounded value such as 36.3.
DATEDIF is a long-standing Excel function that remains available in Microsoft 365, though it may not appear in the function autocomplete list as you type. Enter the function name and its arguments directly. The formula recalculates when Excel updates the workbook, so its result changes as time passes.
Use a real Excel date in A2, not a text string that merely looks like a date. To enter a birth date, type it in a recognizable date format for your regional settings, such as 6/15/1990 in a U.S. configuration, then check that Excel recognizes it as a date. You can select the cell and use Home > Number Format to display it in a clear format.
Calculate age on a chosen date
For reports, eligibility checks, or historical records, you may need age on a particular date rather than age today. Put the birth date in A2 and the date to calculate against in B2. This formula returns completed years as of the date in B2:
With the birth date in A2 and the as-of date in B2, use =DATEDIF(A2,B2,"Y"). If A2 is June 15, 1990, and B2 is May 1, 2026, the result is 35, because the 2026 birthday has not occurred yet.
This arrangement is useful when a worksheet needs to preserve an age calculation for a past event. Unlike TODAY(), the date in B2 does not change each day. It also makes the formula easier to check: readers can see both dates used in the calculation. If B2 is earlier than the birth date in A2, DATEDIF returns an error, so make sure the dates are in chronological order.
Show years and months, or age in months
Some situations need more detail than completed years. DATEDIF can return the number of whole months between dates with the unit “M”. If the birth date is in A2, this formula returns age in completed months as of today:
With the birth date in A2, enter =DATEDIF(A2,TODAY(),"M"). For a birth date of June 15, 1990, on November 3, 2026, the result is 436 completed months: 36 years account for 432 months, plus four completed months since June 15.
To display both completed years and the remaining whole months in one cell, join two DATEDIF results with text. With the birth date in A2, use =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months". For the same June 15, 1990 birth date on November 3, 2026, the result is 36 years, 4 months. The “YM” unit reports the remaining months after complete years; it does not include the extra days. Because this formula joins numbers and words, its result is text rather than a numeric age, which is less suitable for later calculations.
Handle blank cells and invalid dates
A formula copied down a long list can display an unwanted result or error when a birth date is missing. To leave the result cell empty when A2 is blank, wrap DATEDIF in IF. With birth dates in A2:A100, enter this formula in the corresponding result cell on row 2, then fill it down:
With each row’s birth date in column A, use =IF(A2="","",DATEDIF(A2,TODAY(),"Y")). The formula returns a blank when A2 is blank and calculates age when A2 contains a date.
This check does not fix text dates or dates entered in the future. A text value may produce an error because Excel cannot use it as a date, while a future date makes the start date later than TODAY(). Check questionable entries by selecting the cell and inspecting the formula bar, or try changing its number format: a recognized date will usually display differently from a text value. Also verify the date order when importing data; 03/04/1990 can mean March 4 or April 3 depending on regional conventions.
Use an explicit formula instead of DATEDIF
If you prefer to see how the birthday comparison works, calculate the difference between calendar years and subtract one when this year’s birthday has not arrived. With the birth date in A2, use =YEAR(TODAY())-YEAR(A2)-IF(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))>TODAY(),1,0). For June 15, 1990, on November 3, 2026, it returns 36; on May 1, 2026, it returns 35.
The formula takes the current year minus the birth year, then checks whether the birthday in the current year is still ahead. It can be helpful for learning or auditing the calculation, but DATEDIF is shorter for most worksheets. This version assumes A2 contains a valid date and does not include a blank-cell guard.
February 29 birthdays deserve a policy check. In a non-leap year, an organization or jurisdiction may treat the birthday as February 28 or March 1 for a particular purpose. Excel’s DATE function rolls an invalid February 29 in a non-leap year into March 1, so the explicit formula above uses that anniversary convention. Do not assume a spreadsheet formula settles a legal or organizational definition of age; confirm the applicable rule when the result affects eligibility or compliance.
Common problems and useful checks
- #NUM!: The start date is later than the end date, or one of the date inputs is not usable as expected. Check that the birth date is not in the future and that the as-of date is later.
- #VALUE!: A date may have been stored as text or the cell may contain another unexpected value. Re-enter or convert it to a recognized Excel date.
- A result that looks wrong by one year: Age in completed years changes on the birthday, not on January 1. Check whether the birthday has passed on the calculation date.
- A strange result in a blank row: Use an IF blank check before DATEDIF, as shown above, and confirm that the formula references the intended row.
- A result that does not update: TODAY() changes when Excel recalculates. Check Formulas > Calculation Options and select Automatic if the workbook is set to manual calculation; you can also recalculate with F9 on Windows.
In Excel for Microsoft 365 on Windows, the formula syntax shown here uses commas between arguments. Excel’s argument separator can depend on regional settings, so some installations require semicolons instead. If a correctly written formula is rejected, check the separator used by other formulas in that installation. On Mac and Excel for the web, the same functions and general formula logic apply; menu placement may vary, and the calculation mode or regional settings can affect what you see.
Choose the right result for the worksheet
For a simple current age, use DATEDIF with TODAY(). For age on an event date, use DATEDIF with a cell containing that date. Use the “M” unit when the question is about completed months, and combine “Y” with “YM” when a readable years-and-months label is useful. These are not interchangeable outputs: years and months are whole units, while a decimal age is an approximation whose value depends on the day-count convention used.
If you need to sort, average, or filter ages, keep the result numeric. A years-and-months label created by joining text is convenient to read but is not a number for those operations. You can also keep the birth date as the source of truth and calculate age in a separate column, rather than storing a typed age that becomes outdated. For a printed snapshot, copy the calculated results and use Paste Special > Values if you need to preserve the ages as of that date; keep the original birth dates if the workbook must support later recalculation.
Frequently Asked Questions
What is the simplest Excel formula to calculate age?
With the birth date in A2, use =DATEDIF(A2,TODAY(),"Y"). It returns completed years as of today.
Does DATEDIF calculate age in completed years?
Yes. With the “Y” unit, it counts whole years between the two dates. It does not round up based on how close the next birthday is.
How do I calculate age as of a date in another cell?
Put the birth date in A2 and the comparison date in B2, then use =DATEDIF(A2,B2,"Y"). The result is age in completed years on the date in B2.
Why is DATEDIF missing from Excel’s function suggestions?
DATEDIF is available in Excel but may not appear in autocomplete. Type the function and its arguments directly, using the appropriate argument separator for your regional settings.
Can Excel calculate age in months?
Yes. With the birth date in A2, use =DATEDIF(A2,TODAY(),"M") for completed months. This returns a numeric total rather than a years-and-months label.
Related
- Excel File Too Big? How to Reduce Workbook File Size Safely
- Excel File Too Large for No Reason? Find the Hidden Data
- Excel File Size Too Large? How to Reduce It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Julian Date Formula: Day of Year and MoreFor a date in A2, use =INT(A2)-DATE(YEAR(A2),1,0) to return its day number within the year: January 1 is…
- Hide Columns in Excel Based on a Formula AutomaticallyExcel formulas cannot hide columns by themselves. To hide or show columns when a formula’s result changes, put…
- Excel Autofill vs Flash Fill: How to Choose the Right ToolExcel AutoFill extends a series or copies cell contents, while Flash Fill recognizes a pattern in your examples…
- Excel Compare Two Sheets for Matching Names: FormulasTo compare names in two worksheets, enter a formula beside the names on the first sheet and check…
- IF Function in Excel: Examples and Practical UsesIn Excel, the IF function checks whether a condition is true and returns one result if it is,…
- Excel A to Z Formula: How to Sort DataWith the values to sort in A2:A20 and an empty output area beginning at C2, enter =SORT(A2:A20) in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.