5 Excel Formulas to Calculate Age From Date of Birth
Excel can calculate chronological age from date of birth in several ways. Need just years? Or the full years-months-days breakdown? These copy-paste formulas do it automatically.
Five formulas, simple to complex. Pick what you need. Our full Excel guide has more advanced stuff too.
Formula 1: Age in Years (Basic)
=DATEDIF(A1, B1, "Y")Gives you complete years between birth date (A1) and target date (B1). Swap B1 for TODAY() to get current age. The simplest option.
The DATEDIF function computes the difference between two dates in the specified unit. The “Y” parameter requests the result in complete years. Partial years are discarded. A child who is 8 years and 11 months old returns 8, not 9.
Formula 2: Age in Years, Months, and Days
=DATEDIF(A1,B1,"Y")&" years, "&DATEDIF(A1,B1,"YM")&" months, "&DATEDIF(A1,B1,"MD")&" days"Returns a formatted text string like “8 years, 3 months, 12 days”. The YM parameter returns months excluding years. The MD parameter returns days excluding months and years. This produces the standard chronological age format used in clinical documentation.
This formula concatenates three DATEDIF results with descriptive text. The result is a text string, not a number. You cannot perform mathematical operations on the result. For calculations, use the individual components separately.
Formula 3: Current Age (Auto-Updating)
=DATEDIF(A1, TODAY(), "Y")Calculates age as of the current date. The TODAY() function updates automatically every time the spreadsheet recalculates. Place birth dates in column A and fill this formula down column B for a complete age list.
This formula is ideal for tracking current ages in a roster or database. Each time you open the spreadsheet, ages update to reflect the current date. No manual updates are required.
Formula 4: Total Months
=DATEDIF(A1, B1, "M")Returns total complete months between dates. Useful for pediatric milestone tracking where monthly intervals are more meaningful than yearly ones. A 15-month-old toddler is meaningfully different from a 12-month-old, even though both are “one year old” in casual terms.
The “M” parameter counts complete months. A child born January 15 who is evaluated on March 14 returns 1 month, not 2, because the second month is not yet complete.
Formula 5: YEARFRAC for Precise Fractional Years
=INT(YEARFRAC(A1, B1, 1))Uses actual day count for precise year calculation. The basis parameter of 1 tells Excel to count actual days in both the numerator and denominator. INT truncates the decimal, returning complete years only.
YEARFRAC is more mathematically precise than DATEDIF for certain date ranges. The function calculates the fraction of a year between two dates using the specified day-count basis. Different bases suit different applications.
Bulk Calculation Tips
To process hundreds of birth dates at once: place all birth dates in column A starting at A1. Enter =TODAY() in cell C1 for current date reference. In B1, enter the combined formula. Press Ctrl+D to fill the formula down column B.
For a permanent reference date, replace TODAY() with a specific date cell. This creates a snapshot of ages at a specific point in time. Update the reference date cell to recalculate ages for a different date.
Troubleshooting Common Errors
#VALUE! error: Usually indicates a text value where a date is expected. Verify that cells contain actual Excel dates, not text that looks like dates. Reformat cells as Date format.
#NUM! error: The birth date is after the target date. Check for data entry errors, especially year transpositions. A birth year of 2025 with a target year of 2020 produces this error.
Wrong age result: Usually caused by date format confusion. Excel stores dates as serial numbers. Different regional settings interpret “01/02/2025” as January 2nd or February 1st. Use unambiguous date formats.
Advanced Applications
Create an age distribution chart by combining DATEDIF with Excel’s charting tools. Group ages into brackets using IF statements or VLOOKUP tables. Generate summary statistics with COUNTIF and AVERAGE functions.
Build an aging report that flags individuals approaching age thresholds. Use conditional formatting to highlight ages within specific ranges. Create automated alerts for upcoming birthdays or milestone ages.
Frequently Asked Questions
Does DATEDIF work in Excel 365?
Yes. DATEDIF is fully supported in Excel 365, Excel 2021, Excel 2019, and Excel Online. It is an undocumented function, so it does not appear in autocomplete. Type it manually.
Why use DATEDIF instead of simple subtraction?
Simple subtraction returns days only. DATEDIF converts that day count into meaningful year-month-day components, which is the standard format for chronological age calculation.
How do I handle leap years in Excel?
DATEDIF and YEARFRAC both handle leap years automatically. You do not need special adjustments for February 29th birthdays. The functions count actual calendar days between dates.
Can I calculate age for thousands of records?
Yes. DATEDIF works efficiently with large datasets. For very large files (tens of thousands of rows), consider using Excel tables or Power Query for better performance.