To calculate age from a date of birth in Excel, compare the birth date with a date you choose as the calculation date.
The right formula depends on whether you need completed years, an age as of today, or a result that updates on a specific date.
Calculate age in completed years with DATEDIF
For a birth date in cell A2, use this formula to return the person’s age in completed years as of today:
=DATEDIF(A2,TODAY(),"Y")
DATEDIF counts full years between two dates.
Someone born on June 15, 2000, is 25 on June 14, 2026, and 26 on June 15, 2026.
The formula accounts for whether the birthday has occurred yet this year.
Enter the date of birth in A2, then enter the formula in another cell, such as B2.
Copy it down to calculate ages for additional rows.
The result is a whole number, so format the result cell as General or Number rather than Date.
Calculate age on a specific date
For a fixed reference date, place that date in B1 and use:
=DATEDIF(A2,B1,"Y")
For example, if A2 contains 6/15/2000 and B1 contains 6/14/2026, the result is 25.
Change B1 to 6/15/2026, and the result becomes 26.
A reference cell is useful for reports that must show ages as of a past or future date instead of recalculating every day.
Make sure the reference date is on or after the birth date.
DATEDIF returns an error when the start date is later than the end date.
Use a formula that avoids DATEDIF
DATEDIF is available in Excel, although it may not appear in the function autocomplete.
For a formula built from commonly documented date functions, use:
=YEAR(TODAY())-YEAR(A2)-IF(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))>TODAY(),1,0)
This subtracts the birth year from the calculation year, then subtracts one if this year’s birthday is still ahead. For most birth dates, it gives completed years; February 29 birthdays need an explicit non-leap-year convention, as described below.
To calculate age as of the date in B1, replace each instance of TODAY() with B1:
=YEAR(B1)-YEAR(A2)-IF(DATE(YEAR(B1),MONTH(A2),DAY(A2))>B1,1,0)
These formulas expect a valid date of birth in A2 and a valid calculation date. For February 29 birthdays, the formula uses DATE to build the birthday in the calculation year, which can normalize February 29 to a date in March in a non-leap year; the formula may then differ from the chosen birthday convention.
If a particular policy defines leap-day birthdays as February 28 or March 1, use a formula or business rule that explicitly applies that convention.
Why not divide days by 365?
A tempting shortcut is to divide the number of days since birth by 365 and round down.
This can give an incorrect age because calendar years are not all 365 days long; leap years add an extra day.
It can also produce an age that changes on the wrong day near a birthday.
Use DATEDIF with the "Y" unit, or the birthday-aware formula above, when the result should represent completed calendar years.
Use a day-based calculation only when you specifically need an approximate age in years.
Handle blank or invalid birth dates
If the birth-date column may contain blank cells, wrap the calculation in an IF test so blank rows stay blank:
=IF(A2="","",DATEDIF(A2,TODAY(),"Y"))
To flag a birth date that is later than today, use:
=IF(A2="","",IF(A2>TODAY(),"Check date",DATEDIF(A2,TODAY(),"Y")))
These examples assume that A2 contains a real Excel date, not text that merely looks like one.
If the formula returns #VALUE!, check the cell’s contents and regional date settings.
For example, 03/04/2000 may be interpreted as March 4 or April 3 depending on the workbook’s locale.
A clear entry such as 4-Mar-2000 or a date created with DATE(2000,3,4) can reduce ambiguity.
Calculate age in years and months
To display completed years and remaining months, combine two DATEDIF calculations:
=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months"
For someone whose last birthday was 4 years and 7 months ago, this returns 4 years, 7 months.
The "YM" unit returns the months left after counting complete years; it does not include remaining days.
To include them, append another calculation using the "MD" unit, while keeping in mind that day counts across month boundaries can be unintuitive.
Compatibility and spreadsheet differences
DATEDIF, TODAY, YEAR, MONTH, DAY, DATE, and IF work in modern desktop versions of Excel and are also available in Google Sheets.
Formula argument separators depend on regional settings: some installations use semicolons, so a localized version may need =DATEDIF(A2;TODAY();"Y").
TODAY() updates when the spreadsheet recalculates, so an age based on it can change as time passes.
Use a date stored in a cell when you need a stable reporting date.
In Google Sheets, the same formulas work, but the spreadsheet’s locale and recalculation settings can affect date interpretation and when volatile functions refresh.
Common formula problems
- The result looks like a calendar date: Change the result cell’s number format to General or Number.
- The formula shows an error: Confirm the birth date is a valid date value and is not after the calculation date.
- The result is one year too high or low: Check whether the birthday has passed as of the reference date and verify the date’s month and day interpretation.
DATEDIFis not suggested: Type the function directly; autocomplete may not list it.- Blank rows show a number: Use the blank-checking version of the formula.
For most worksheets, =DATEDIF(A2,TODAY(),"Y") is the simplest way to calculate completed age today.
Use a reference-date cell when the age must be calculated as of a chosen date, and check date values and formatting if the output seems wrong.