To calculate age from a birthdate in Excel, subtract the birthdate from today's date and divide by 365.25, or use the dedicated function =DATEDIF(birthdate, TODAY(), "Y") to return completed years directly in a single cell. The DATEDIF approach is the most reliable because Excel stores every date as a serial number counted in whole days from January 1, 1900, which means date subtraction always produces an exact integer number of days. For a richer breakdown into years, months, and days, you combine three DATEDIF calls with the unit codes "Y", "YM", and "MD". A typical cell formula looks like =DATEDIF(A2, TODAY(), "Y") & " years, " & DATEDIF(A2, TODAY(), "YM") & " months, " & DATEDIF(A2, TODAY(), "MD") & " days", and it returns the age string for the date sitting in cell A2. If you only want whole years (which is enough for age-verification columns, anniversaries, and most HR spreadsheets), the single-argument version is more than enough.
People usually arrive at this question with a real spreadsheet in front of them: a customer list, a roster of students, a batch of new employees, or a contact database imported from a CRM. The pattern is always the same — every row carries a date of birth, and you need a clean "Age" column beside it. Excel does not have a single named Age function, which is why every tutorial on the topic leads back to either DATEDIF, YEARFRAC, or a subtraction-plus-division trick. The subtraction trick is quick but less accurate for ages under one year, while YEARFRAC returns fractional years that you must round yourself. DATEDIF sits in the middle: it gives you an integer answer in the units you ask for, with no rounding required.
The Excel formula that returns age in whole years
Open a blank workbook and type a date of birth into cell A2 (for example, 1990-06-15). In cell B2, enter the formula:
- Type =DATEDIF(A2, TODAY(), "Y") and press Enter.
- Excel returns an integer representing the number of completed years between the birthdate in A2 and today's date.
- Drag the fill handle in the bottom-right corner of cell B2 down to apply the formula to every row in your table.
- Format the new column as a number with zero decimal places if Excel decides to display it as a decimal.
The third argument inside DATEDIF is called the unit code, and it controls the precision of the answer:
| Unit code | What DATEDIF returns | Typical use |
|---|---|---|
| "Y" | Number of completed years | Standard age in years |
| "M" | Number of completed months | Total months lived |
| "D" | Number of completed days | Exact day count |
| "YM" | Months remaining after the last full year | Pair with "Y" for years-and-months age |
| "MD" | Days remaining after the last full month | Pair with "Y" and "YM" for years-months-days |
| "YD" | Days remaining after the last full year | Pair with "Y" for years-and-days age |
The "YM" and "MD" codes are particularly useful because they let you skip past completed years or completed months without ever crossing back through a full year. That is how you write the years-months-days age you would normally read off a passport or ID card.
Building a years, months, and days age in one cell
Sometimes a single integer is not enough — applications, certificates, and HR forms often want "32 years, 4 months, 17 days" instead of just "32". To get that format, you concatenate three separate DATEDIF calls into a single string. Place the birthdate in cell A2 again, and put this formula in cell B2:
=DATEDIF(A2, TODAY(), "Y") & " y, " & DATEDIF(A2, TODAY(), "YM") & " m, " & DATEDIF(A2, TODAY(), "MD") & " d"
Walk through what each piece does: the first DATEDIF returns the integer year count using "Y"; the second uses "YM" to count any leftover months past the last birthday; the third uses "MD" to count any leftover days past the last month-birthday. The ampersand character & is Excel's string-concatenation operator, so it glues the numbers to the literal text labels you place in quotes. If you prefer a friendlier date display for the formula bar, you can also wrap each DATEDIF call in a TEXT function to control leading zeros: TEXT(DATEDIF(A2, TODAY(), "Y"), "0") will force a two-digit year output as long as the value is greater than nine.
Other Excel methods worth knowing
Beyond DATEDIF there are two more approaches that show up regularly in older spreadsheets and in tutorials written before DATEDIF became familiar. The first is the YEARFRAC trick: =INT(YEARFRAC(A2, TODAY(), 1)). The third argument of YEARFRAC is the day-count basis — 1 means "actual/actual", which mimics how leap years are handled in real-world aging. The INT() wrapper knocks the fractional years down to a clean integer. This produces the same result as DATEDIF for ages above one year, but it can return slightly different answers when the person has not yet had their birthday in the current calendar year.
The second approach is the subtraction-plus-division method, which is the most transparent for beginners: =INT((TODAY() - A2) / 365.25). The numerator gives the total days lived because both TODAY() and a typed date are serial numbers, and dividing by 365.25 converts that day count into a rough year count. The INT() wrapper rounds down, which is what "completed years" actually means. The 365.25 divisor is a rough leap-year average, so the answer is off by a couple of days for older ages — for HR or age-verification work this is rarely a problem, but it is worth knowing the discrepancy exists. If you want the same precision without typing formulas, the Age Calculator on Lizely breaks the result into years, months, days, total months, total weeks, total days, and a countdown to your next birthday in a single view.
Common errors and how to fix them
The single most common age-formula error in Excel is a #NUM! result from DATEDIF. DATEDIF returns this error when the start date is later than the end date, so it is almost always caused by one of two things: either the birthdate cell is empty (Excel treats blank as the number 0, which is January 0, 1900 — a date earlier than TODAY, so the result is huge but mathematically valid until the data runs out), or the birthdate has been entered as a text string instead of a real Excel date. To diagnose the issue, click the birthdate cell, look at the formula bar, and check whether the value right-aligns (a real date) or left-aligns (a text string). If it is text, the =DATEVALUE(A2) wrapper will convert it back to a serial number.
The second common error is the "start_date is later than end_date" message, which appears when the formula has been set up to calculate the age as of a future "as-of" date that is earlier than the birthdate. This usually happens when you copy a formula intended for today's date into a cell where you wanted to evaluate an age against, say, January 1, 2020. Replace TODAY() with the hard-coded "as-of" date, or reference another cell that holds it.
The third pitfall is the #VALUE! error, which appears when one of the references inside DATEDIF holds text instead of a date. Wrap the arguments in DATEVALUE() if you cannot format the source cells as dates, or use Text to Columns on the source column to force everything into date format. Once the column is correctly recognized as dates, DATEDIF will return numbers in a single step.
When an online tool makes more sense
Spreadsheets are fantastic when you have hundreds or thousands of rows to update at once, but most people searching for "how to calculate age from birthdate in Excel" actually have a single date in mind: their own birthday, a child's age, a parent's age, a deadline based on age, or a legal-eligibility cutoff. In those one-off cases, an online Age Calculator can return an answer faster than switching into Excel, typing a formula, and waiting for it to evaluate. The Lizely tool also handles the precision parts that DATEDIF requires three calls to compute — years, months, days, total months, total weeks, total days, and a live countdown to the next birthday — without you needing to remember any unit codes.
A second scenario where an online calculator pulls ahead is when you want an age "as of" a date in the past or future. Replacing TODAY() with a hard-coded date works in Excel, but you have to edit and re-enter the formula each time you change the reference date. A web tool typically offers a second date field alongside the birthdate field, so toggling the "as-of" date produces a new answer with no formula editing. This is particularly handy for legal scenarios such as "how old was this person on the date of contract signing", which you can read about in our Excel and online age guide.
Putting it all together
Choose your approach based on the size of your dataset and the precision you need. For a quick one-row answer, =DATEDIF(A2, TODAY(), "Y") is the cleanest Excel formula and returns completed years. For a years-months-days string, concatenate three DATEDIF calls with the "Y", "YM", and "MD" unit codes separated by & and label text. For a rough integer years that works in older spreadsheets without DATEDIF, fall back to =INT(YEARFRAC(A2, TODAY(), 1)) or =INT((TODAY() - A2) / 365.25). And for personal one-off answers where you would rather skip the formula entirely, head to the Lizely Age Calculator, type the birthdate, and read the breakdown live.