If you work in Excel regularly — whether you're managing student rosters, client caseloads, or HR records — you don't need to leave your spreadsheet to calculate chronological age. This guide gives you a working DATEDIF formula you can copy and paste, plus a walkthrough of how it works and what to watch out for.
The Excel Formula for Chronological Age
Copy this formula into any empty cell. It returns a person's age in years, months, and days:
=DATEDIF(B2,C2,"y") & " years, " & DATEDIF(B2,C2,"ym") & " months, " & DATEDIF(B2,C2,"md") & " days" In this formula, B2 is the cell containing the date of birth and C2 is the cell containing the as-of date (the date you want to calculate age on). You can replace C2 with =TODAY() to always compute age relative to the current date.
DATEDIF takes three arguments: a start date, an end date, and a unit code. Here's what each unit code returns:
| Unit Code | Returns | Example Output |
|---|---|---|
"y" | Complete years between dates | 7 |
"ym" | Remaining months after full years | 3 |
"md" | Remaining days after full months | 23 |
"m" | Total complete months (not used in the main formula) | 87 |
"d" | Total days between dates (not used in the main formula) | 2,667 |
The & operator joins the three DATEDIF results into a single readable string like "7 years, 3 months, 23 days".
Step-by-Step: How to Set It Up
- Column A: Enter the person's name (optional, for your reference).
- Column B: Enter the date of birth. Right-click the column, choose Format Cells, and set the format to Date. Example:
3/15/2018. - Column C: Enter the as-of date (the test date or reference date). You can type a specific date or enter
=TODAY()to use today's date automatically. - Column D: Paste the DATEDIF formula shown above. It will display the chronological age as a text string.
- Drag down the formula to apply it to every row in your list.
Tip: If you need separate numeric columns for years, months, and days (e.g., for sorting or further calculation), use three separate formulas: =DATEDIF(B2,C2,"y") in one column, =DATEDIF(B2,C2,"ym") in the next, and =DATEDIF(B2,C2,"md") in the third.
Common Excel Chronological Age Formula Errors
- #NUM! error: DATEDIF requires the start date to be earlier than the end date. If the date of birth is in a later cell or is accidentally a future date, Excel returns this error. Double-check that your date columns are in the correct order.
- Text-formatted dates: If a date column is formatted as Text or General, Excel may not recognize the values as real dates. Select the column, set the format to Date, and re-enter or re-paste the dates.
- Regional date format mismatch: Excel interprets dates based on your system's locale. If your data uses dd/mm/yyyy but your system expects mm/dd/yyyy (or vice versa), dates like 03/15/2018 vs. 15/03/2018 can cause silent errors. Use the Text to Columns wizard or
DATEVALUEto force correct parsing. - DATEDIF is undocumented: Microsoft does not list DATEDIF in Excel's formula autocomplete or official help in all versions. It still works — just type it manually. It has been supported since Excel 2000 and continues to function in Excel 365.
Excel vs Using an Online Chronological Age Calculator
Both approaches produce the same result. The difference is workflow. Excel is ideal for batch calculations — computing age for an entire class roster, client list, or employee database in one pass. You can drag formulas down hundreds of rows instantly. An online calculator is faster for one-off checks: enter a single date of birth, get an immediate answer, no spreadsheet setup needed. Many professionals use both — Excel for bulk processing, and an online tool like the one on our homepage for quick verification of individual results.
Try It Instantly Instead
If you only need the chronological age for one person right now, skip the spreadsheet. Use our free online chronological age calculator to get an exact result in seconds. Enter a date of birth and an optional test date, and see the age in years, months, and days immediately — no formula required.