How to Calculate Age from Date of Birth in Excel
Calculating a person’s age from a birth‑date is a common task in both personal and business spreadsheets. Below you’ll find the most reliable Excel‑only methods, how to keep the results clean when the source data is imperfect, and a few performance tips for large tables. Google Sheets equivalents are noted where the functions exist in both platforms.
1. Using DATEDIF (Excel 2010 – 2023, Excel 365/2021)
DATEDIF is an undocumented but fully supported function that returns the difference between two dates in a variety of units. It works in all recent desktop versions of Excel and in Excel for the web, but it is not available in Google Sheets.
Syntax
=DATEDIF(start_date, end_date, unit)
| Argument | Description |
|---|---|
| start_date | Birthdate (must be a real Excel date). |
| end_date | Usually TODAY() so the age updates automatically. |
| unit | "Y" = whole years, "YM" = remaining months, "MD" = remaining days, etc. |
1‑A. Whole‑years age (the most common requirement)
=DATEDIF(A2, TODAY(), "Y")
| A (Birthdate) | B (Age) |
|---|---|
| 1995‑04‑23 | 31 |
| 2000‑11‑05 | 26 |
| 1982‑07‑19 | 44 |
Result assumes today is 2026‑10‑14.
1‑B. Full age string (years, months, days)
=DATEDIF(A2, TODAY(), "Y") & "y " &
DATEDIF(A2, TODAY(), "YM") & "m " &
DATEDIF(A2, TODAY(), "MD") & "d"
Result for 1995‑04‑23 → 31y 5m 21d.
1‑C. Common pitfalls
| Issue | Why it happens | Fix |
|---|---|---|
| Birthdate stored as text | DATEDIF expects a serial date. |
Convert with DATEVALUE or coerce using --A2. |
| Blank cell | A blank counts as date 0 (1900), so DATEDIF returns about 126 instead of an error. | Wrap with IF(A2="","",DATEDIF(...)). |
| Future birthdate | DATEDIF returns #NUM! when the start date is after the end date. |
Wrap in IF(A2>TODAY(),"Future date",DATEDIF(A2,TODAY(),"Y")). |
| Leap‑year birthday (Feb 29) | Works, but “birthday this year” logic needs extra handling. | Use EDATE or DATE(YEAR(TODAY()),2,29) with IFERROR. |
2. Dynamic Age with TODAY()
Embedding TODAY() as the end date makes the age auto‑update each day the workbook recalculates.
=DATEDIF(A2, TODAY(), "Y")
| A (DOB) | B (Dynamic Age) |
|---|---|
| 1990‑04‑15 | 36 |
| 2005‑11‑30 | 20 |
| 1978‑07‑02 | 48 |
All results are for 2026‑10‑14.
Why it works – DATEDIF counts whole years only, matching the usual definition of age, and TODAY() refreshes automatically.
Note – In Google Sheets the same formula works because DATEDIF exists there as well.
3. Handling Edge Cases and Invalid Dates
Real‑world data often contains blanks, text, or impossible dates. The following patterns keep the age column tidy.
| Issue | Remedy | Example |
|---|---|---|
| Blank cell | Return empty string | =IF(A2="","",DATEDIF(A2,TODAY(),"Y")) |
| Text instead of date | Test with ISNUMBER and suppress errors |
=IFERROR(IF(ISNUMBER(A2),DATEDIF(A2,TODAY(),"Y"),""),"") |
| Future birthdate | Show a label | =IF(A2>TODAY(),"Future date",DATEDIF(A2,TODAY(),"Y")) |
| Invalid date string (e.g., “02/30/2024”) | Convert with DATEVALUE inside IFERROR |
=IFERROR(DATEDIF(DATEVALUE(A2),TODAY(),"Y"),"Invalid date") |
4. Using IFERROR to Manage Errors
Wrap the age formula in IFERROR to replace #VALUE! (text instead of a date) or #NUM! (future date) with a friendly label.
=IFERROR(DATEDIF(A2, TODAY(), "Y"), "Check date")
| A (DOB) | B (Result, today = 2026‑10‑14) |
|---|---|
| 1990‑04‑15 | 36 |
| 2005‑12‑01 | 20 |
| abc | Check date |
| 2027‑01‑01 | Check date |
Tip – A blank cell does not trigger an error, so add IF(A2="","",...) if you have empty rows.
5. Formatting the Result
Whether you need a plain number or a labeled string, the same functions work in Excel and Google Sheets.
| Goal | Formula | Result example |
|---|---|---|
| Whole years | =DATEDIF(A2,TODAY(),"Y") |
31 |
| Text with “years” | =DATEDIF(A2,TODAY(),"Y") & " years" |
31 years |
6. Alternative Method: YEARFRAC + INT
YEARFRAC(start_date, end_date, [basis]) returns a decimal year count that respects actual month lengths and leap years. Wrap it in INT for a whole‑number age. Do not use ROUND: it rounds 30.6 up to 31 before the birthday.
=INT(YEARFRAC(A2, TODAY(), 1))
| A (DOB) | B (Age) |
|---|---|
| 1990‑04‑15 | 36 |
| 2005‑11‑30 | 20 |
| 1978‑02‑02 | 48 |
basis = 1 = Actual/Actual day‑count. The formula works in Excel 2007+ and Google Sheets, with no special version requirements.
When to use – If you need the fractional year for other calculations (e.g., prorated service time) before rounding, YEARFRAC is the most compact option.
7. Performance Tips for Large Datasets
Calculating ages for tens of thousands of rows can slow a workbook if formulas are volatile or reference whole columns.
| Tip | Why it helps | Quick implementation |
|---|---|---|
| Avoid whole‑column references | Limits the recalculation range. | Use A2:A10000 instead of A:A. |
Store TODAY() once |
TODAY() is volatile; each call forces a full recalc. |
In a hidden cell (e.g., Z1) put =TODAY() and reference $Z$1. |
| Coerce text dates once | Repeated DATEVALUE calls add overhead. |
Helper column =DATEVALUE(A2) → use that helper in the age formula. |
| Prefer simple arithmetic over array formulas | Array formulas evaluate the entire array each time. | =INT(($Z$1‑B2)/365.25) is faster than {=DATEDIF(... )}. |
| Set calculation to Manual while loading data | Prevents recalculation after every row insert. | Formulas → Calculation Options → Manual, then press F9 when the import finishes. |
| Use Excel Tables | Structured references auto‑extend and keep formulas readable. | Convert range to a table (Ctrl+T) and use =[@DOB] inside the formula. |
Sample formula for a table named People
=INT(($Z$1 - [@DOB]) / 365.25)
$Z$1 holds TODAY(). The formula returns a whole‑number age for each row with minimal overhead.
8. FAQ
What is the most reliable formula to calculate age in Excel?
=DATEDIF(A2, TODAY(), "Y") is the most reliable because it counts whole years only, automatically handles leap years, and works in all desktop and web versions of Excel (no additional add‑ins required).
How does the DATEDIF function handle leap years?
DATEDIF treats February 29 as a valid date. When calculating whole years ("Y"), it counts a full year only after the anniversary date passes, so a person born on Feb 29 1996 will turn 30 on Feb 28 2026 and 31 on Mar 1 2027, matching the usual legal interpretation.
Can I calculate age in Google Sheets as well?
Yes. Google Sheets supports DATEDIF, YEARFRAC, TODAY(), IFERROR, and most of the same text‑handling functions used above. The only notable difference is that Sheets’ SPLIT accepts only a plain‑text delimiter (no regex), but that does not affect age calculations. All formulas shown here work in Sheets unchanged.