Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate age from a date of birth
Topic:Date arithmeticExcel & Google Sheets
=DATEDIF(DATE(A2,B2,C2),DATE(D2,E2,F2),"Y")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Birth year | Birth month | Birth day | As-of year | As-of month | As-of day |
| 2000 | 9 | 17 | 2026 | 9 | 16 |
=DATEDIF(DATE(A2,B2,C2),DATE(D2,E2,F2),"Y")→25
Both expressions are checked against an independently calculated expected result in HyperFormula. This is a sample execution check, not native Excel or Google Sheets certification.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter the two dates as numeric year, month and day components in A2:F2.
- 2DATE constructs spreadsheet dates without depending on an ambiguous text format such as 09/10/2000.
- 3Place the formula outside the input range. The birthday has not occurred yet in 2026, so the result is 25 completed years.
- 4For a live age calculation, replace DATE(D2,E2,F2) with TODAY(). Keep the fixed date when recreating a historical report.
Tips
- Validate the input calendar dates first: DATE normalizes out-of-range months and days rather than rejecting every invalid date.
- The birth date must not be later than the as-of date. Leap-day birthdays may have legal or policy-specific anniversary rules; confirm those separately for eligibility decisions.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Why not subtract birth year from current year?
That overstates age before the birthday. DATEDIF counts completed years.
Why is the result displayed as a date?
Change the output cell to Number or General; the result is a count of years.
More on Date arithmetic
See all →Reference
TODAY / DATEDIF
Work with the current date and measure the gap between two dates.
How-toHow to calculate days between two dates
Subtract one date from another to get the number of days, or count working days with NETWORKDAYS. Works in both apps.
ReferenceDATEDIF
Calculates the difference between two dates in years, months, or days using a unit code string.
How-toHow to add months to a date
Shift a date forward or back by a number of months with EDATE — it handles month lengths correctly. Works in both apps.
How-toHow to flag overdue dates
Mark rows whose due date has passed by comparing it to TODAY() with IF. Works the same in Excel and Google Sheets.
How-toHow to calculate age from a birthday
Use DATEDIF with TODAY() to calculate a person's exact age in years from a birth date cell in Excel and Google Sheets.
Sources and compatibility
Reviewed 2026-09-16