Excel Guide
How to Calculate Chronological Age in Excel
Excel's DATEDIF function calculates the exact difference between two dates in years, months, or days — making it the right tool for chronological age. This guide covers every formula variant, with a worked example and Google Sheets compatibility notes.
Setting Up Your Spreadsheet
| Cell | Content | Example value |
|---|---|---|
| A1 | Birth date | 9/15/2019 |
| B1 | Reference date (test date or today) | 6/8/2026 |
| C1 | Age formula (see below) | 6y 8m 24d |
Both A1 and B1 must be formatted as Date cells (not text). If you see a number instead of a date, select the cell → Format Cells → Date.
Step-by-Step Instructions
Enter the birth date in A1
Type the birth date and press Enter. Make sure Excel recognizes it as a date (it should right-align in the cell).
Enter the reference date in B1
Type the assessment or reference date. Use TODAY() if you want the current date to update automatically.
Type the DATEDIF formula in C1
Type the combined formula below — it returns the full age as a text string in the format "Xy Ym Zd".
Press Enter
Excel will display the chronological age. The formula does not appear in autocomplete — this is normal for DATEDIF.
Complete Formula Reference
Assumes birth date in A1 and reference date in B1. Example: born Sept 15, 2019 / reference June 8, 2026 → age 6;8;24.
Full years only
=DATEDIF(A1, B1, "Y")Returns the number of complete years.
Remaining months (after years)
=DATEDIF(A1, B1, "YM")Months remaining after subtracting full years.
Remaining days (after years + months)
=DATEDIF(A1, B1, "MD")Days remaining after subtracting full years and months.
Total complete months
=DATEDIF(A1, B1, "M")All months from birth to reference date.
Total complete days
=B1-A1Exact number of days between the two dates.
Combined Y;M;D in one cell
=DATEDIF(A1,B1,"Y")&"y "&DATEDIF(A1,B1,"YM")&"m "&DATEDIF(A1,B1,"MD")&"d"Displays full age as a text string.
Age using today's date (auto-updates)
=DATEDIF(A1, TODAY(), "Y")&"y "&DATEDIF(A1,TODAY(),"YM")&"m "&DATEDIF(A1,TODAY(),"MD")&"d"Replace B1 with TODAY() for a live age calculation.
Google Sheets Compatibility
All DATEDIF formulas work identically in Google Sheets.
No changes needed — copy and paste the same formulas directly into Google Sheets. DATEDIF is fully documented and supported in Google Sheets (unlike Excel, where it is undocumented but still works).
Common Errors and Fixes
Error: #NUM! error
The start date (A1) is later than the end date (B1). Swap the dates so birth date is in A1.
Error: #VALUE! error
One or both cells contain text instead of a proper date. Re-enter as a date or format the cell as Date.
Error: Formula shows as text
The cell is formatted as Text. Change to General or Number, then re-enter the formula.
Error: DATEDIF not in autocomplete
This is normal — DATEDIF is undocumented in Excel. Type it manually; it works correctly despite not appearing in suggestions.
Error: Wrong day count with "MD"
The "MD" unit is known to have edge-case bugs in older Excel versions. For production use, calculate days separately as =B1-A1-DATEDIF(A1,B1,"Y")*365-DATEDIF(A1,B1,"YM")*30 or use WiseAgeCalc for guaranteed accuracy.
Frequently Asked Questions
What Excel formula calculates age in years, months, and days?
Use three DATEDIF calls: =DATEDIF(A1,B1,"Y") for years, =DATEDIF(A1,B1,"YM") for remaining months, =DATEDIF(A1,B1,"MD") for remaining days. Combine: =DATEDIF(A1,B1,"Y")&"y "&DATEDIF(A1,B1,"YM")&"m "&DATEDIF(A1,B1,"MD")&"d"
Does DATEDIF work in Google Sheets?
Yes. DATEDIF works with the same syntax in Google Sheets. It is fully documented and supported there, unlike Excel where it is undocumented but still functional.
Why doesn't DATEDIF appear in Excel's autocomplete?
DATEDIF is a legacy undocumented function (originally from Lotus 1-2-3). It does not show in autocomplete but works correctly when typed manually. It is supported in all modern Excel versions.
How do I calculate total months in Excel?
Use =DATEDIF(A1,B1,"M") to get the total complete months between two dates. This counts all months from birth to reference date.
How do I use today's date as the reference?
Replace B1 with TODAY() in any DATEDIF formula. Example: =DATEDIF(A1,TODAY(),"Y") gives current full years. The result updates automatically each day.
Skip Excel — Use the Free Calculator
WiseAgeCalc calculates exact chronological age in seconds, with no formula setup required. Results are in Y;M;D format ready for any assessment protocol.
Related Guides
How to Calculate Chronological Age
Manual step-by-step method, borrowing technique, and months formula.
Chronological Age in Months
Convert Y;M;D to total months — formula and conversion table.
Age Calculator for Testing
Exact age for Pearson, Brigance, Bayley, and other assessments.
Child Chronological Age
School enrollment, IEP documentation, and pediatric records.