The formula
Age in yearsExcelGoogle Sheets
=DATEDIF(A2,B2,"Y")In D2 → 36
Years, months, daysExcelGoogle Sheets
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"In D3 → 36 years, 1 months, 23 days
Alternative with YEARFRACExcelGoogle Sheets
=INT(YEARFRAC(A2,B2,1))In D4 → 36
✓ Tested. All 3 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date of birth | As of | ||
| 2 | Aug 23, 1990 | Oct 16, 2026 | 36 | |
| 3 | 36 years, 1 months, 23 days | |||
| 4 | 36 |
How it works
- A2
- Date of birth.
- B2
- The "as of" date. Replace with TODAY() for today's age.
- "Y"
- Complete years. "YM" = months after the last full year, "MD" = days after the last full month.
Common errors and fixes
Why does the formula return #NUM!?
The end date is before the start date.
Odd "MD" results?
Microsoft's DATEDIF documentation recommends against the "MD" unit because of known limitations. Check month-end edge cases.
DATEDIF not in the function list?
It's undocumented in Excel's autocomplete but works when typed.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.