Subtracting birth years can make someone a year older before their birthday. To calculate age with an Excel formula, compare the full birth date with a reference date instead. The DATEDIF function counts completed years, so the answer changes on the birthday rather than on January 1. You can calculate a person’s age using Excel for a fixed date or for today.
Age in Excel using a reference date
Put the birth date in A2 and the date you want to measure against in B2. Enter this formula in C2:
=DATEDIF(A2,B2,"Y")
Press Enter. The "Y" argument asks for complete years between the two dates. For example, enter 6/15/2000 in A2 and try these reference dates in B2:
| Reference date | Result | Why |
|---|---|---|
| 6/14/2025 | 24 | The birthday has not arrived. |
| 6/15/2025 | 25 | The 25th birthday has arrived. |
| 6/16/2025 | 25 | The next full year is not complete. |
This excel formula for age uses a chosen date, which is useful for a report or eligibility review tied to a particular day. If you need the same reference date for many rows, put it in B1 and use =DATEDIF(A2,$B$1,"Y") in C2. The dollar signs keep B1 fixed as you copy this Excel age formula down; A2 changes for each row.
Use today’s date when the answer should change
To calculate age in excel as of the current date, replace B2 with TODAY():
=DATEDIF(A2,TODAY(),"Y")
The calculated age in your Excel sheet updates as Excel recalculates on later days. That is convenient for a live list, but not for a report that must preserve an age as of a past date. For that report, enter the reference date explicitly. If results do not update, check that Excel calculation is set to Automatic.
Handle blanks and invalid entries
This excel formula for age assumes both inputs are valid dates and the birth date is not later than the reference date. For Excel age calculations with incomplete entries, use this version in C2:
=IFERROR(IF(OR(A2="",B2=""),"",IF(OR(NOT(ISNUMBER(A2)),NOT(ISNUMBER(B2))),"Check dates",IF(OR(A2<1,B2<1,A2>B2),"Check dates",DATEDIF(A2,B2,"Y")))),"Check dates")
This Excel formula for calculating completed years leaves the result blank if either input is blank. For text, error values, nonpositive numbers, a birth date after the reference date, or a date calculation Excel cannot perform, it displays Check dates. The result is a prompt to inspect the inputs, not an age of zero. To keep the reference date fixed while copying, replace each B2 in the formula with $B$1.
Excel stores dates as numbers. ISNUMBER checks for a number, not whether that number is truly a birth date. An accidental numeric entry can still look like a date after formatting. Check the displayed dates against the source data; the formula cannot verify their meaning. It also treats a formula returning an empty string as blank.
If the result looks wrong
- Dates appear as text: Re-enter them as dates Excel recognizes, using the U.S. month/day/year format. Changing the cell’s appearance to Date does not reliably convert text into a date value.
- The answer looks like a date: Format the result cell as General or Number. The formula returns a count of years, not another calendar date.
- A date is before birth: Correct the reference date or birth date. The basic formula returns
#NUM!when its start date is later than its end date. - The person was born on February 29: Test the result against the rule your task uses for birthdays in non-leap years. Legal or administrative rules can differ; do not assume a spreadsheet convention answers that separate question.
Microsoft’s DATEDIF documentation describes "Y" as complete years and notes limitations in other units. For a reliable age calculation in Excel, use valid date values, choose the correct reference date, and check the result around the birthday.