How to Calculate Age in Excel
Need to calculate someone's age from their date of birth in Excel? This guide covers the most accurate formulas including DATEDIF, YEARFRAC, and simple methods to get age in years, months, and days.
Quick Answer
To calculate age in Excel, use the DATEDIF formula: =DATEDIF(A2,TODAY(),"Y") where A2 contains the birthdate. This returns the age in complete years.
For age in years, months, and days, use: =DATEDIF(A2,TODAY(),"Y")&" yrs "&DATEDIF(A2,TODAY(),"YM")&" mo"
Method 1: DATEDIF Function (Recommended)
The DATEDIF function is the most accurate way to calculate age in Excel. It calculates the difference between two dates and returns the result in your chosen unit (years, months, or days).
Formula Syntax
=DATEDIF(start_date, end_date, unit)Age in Years
=DATEDIF(A2, TODAY(), "Y")DATEDIF Unit Arguments
| Unit | Returns | Example Output |
|---|---|---|
| "Y" | Complete years between dates | 25 |
| "M" | Complete months between dates | 306 |
| "D" | Days between dates | 9327 |
| "YM" | Months, ignoring years | 6 (partial months) |
| "MD" | Days, ignoring months and years | 15 (partial days) |
| "YD" | Days, ignoring years | 197 |
Note: DATEDIF is an undocumented function in Excel, so it will not appear in autocomplete. However, it works correctly when typed manually and has been available since Excel 2000.
Method 2: YEARFRAC Function
The YEARFRAC function returns the fraction of a year between two dates. Combined with INT or ROUNDDOWN, it provides an accurate age calculation that handles leap years correctly.
Formula
=INT(YEARFRAC(A2, TODAY(), 1))Alternative with ROUNDDOWN
=ROUNDDOWN(YEARFRAC(A2, TODAY(), 1), 0)Understanding the Basis Argument
The third argument (1 in our formula) specifies how to count days:
- 0 or omitted: US (NASD) 30/360
- 1: Actual/actual (most accurate for age)
- 2: Actual/360
- 3: Actual/365
- 4: European 30/360
Using basis 1 (actual/actual) is recommended for age calculations because it accounts for the exact number of days in each month and handles leap years properly.
Why INT or ROUNDDOWN? YEARFRAC returns a decimal (e.g., 25.53 years). INT() or ROUNDDOWN() converts this to complete years (25), which is how we typically express age.
Method 3: Simple Division (Quick Estimate)
For a quick approximation, you can divide the number of days between dates by 365.25 (accounting for leap years):
Formula
=INT((TODAY()-A2)/365.25)Accuracy Note: This method can be off by 1 day near birthdays because 365.25 is an approximation. For critical applications, use DATEDIF or YEARFRAC instead.
Calculate Age in Years, Months, and Days
For a complete age breakdown (e.g., "25 years, 6 months, 15 days"), combine multiple DATEDIF functions:
Separate Cells Method
Years (Cell B2)
=DATEDIF(A2, TODAY(), "Y")Months (Cell C2)
=DATEDIF(A2, TODAY(), "YM")Days (Cell D2)
=DATEDIF(A2, TODAY(), "MD")Combined Formula (Single Cell)
Full Age Formula
=DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days"This produces output like: 25 years, 6 months, 15 days
| Birthdate | Years | Months | Days | Combined |
|---|---|---|---|---|
| 15-Jun-1998 | 27 | 6 | 26 | 27 years, 6 months, 26 days |
| 01-Jan-2000 | 26 | 0 | 10 | 26 years, 0 months, 10 days |
Calculate Age on a Specific Date
Instead of calculating age as of today, you can calculate age as of any specific date by replacing TODAY() with your target date.
Age as of a specific date
=DATEDIF(A2, "2025-12-31", "Y")Age as of date in cell B2
=DATEDIF(A2, B2, "Y")This is useful for:
- Calculating age at a specific event date
- Determining eligibility based on a cutoff date
- Historical age calculations
Formula Comparison
Here is how the different methods compare for accuracy and ease of use:
| Method | Formula | Accuracy | Best For |
|---|---|---|---|
| DATEDIF | =DATEDIF(A2,TODAY(),"Y") | 100% | Most calculations |
| YEARFRAC | =INT(YEARFRAC(A2,TODAY(),1)) | 100% | Alternative to DATEDIF |
| Division | =INT((TODAY()-A2)/365.25) | ~99.8% | Quick estimates |
Recommendation: Use DATEDIF for most cases. It is the most intuitive and handles all edge cases correctly. YEARFRAC is equally accurate and useful if you need decimal years.
Tips & Best Practices
Use proper date formats
Ensure birthdates are stored as Excel dates, not text. Use DATE(year,month,day) or format cells as Date if needed.
Handle leap year birthdays
Excel treats February 29 birthdays as March 1 in non-leap years. Both DATEDIF and YEARFRAC handle this correctly.
Validate date order
DATEDIF returns an error if the start date is after the end date. Add error handling with IFERROR() for robustness.
Consider timezone differences
TODAY() returns the date based on your system timezone. For critical applications, use a fixed date reference.
Format results appropriately
Age in years should be formatted as a number, not a date. Use Format Cells > Number if Excel interprets results as dates.
Document your formulas
Since DATEDIF is undocumented, add a comment or note explaining the formula for future users of your spreadsheet.
Troubleshooting
#VALUE! Error
- Start date after end date: DATEDIF requires the start date to be before the end date. Check your date order.
- Text instead of date: The cell may contain text that looks like a date. Convert it using =DATEVALUE() or re-enter as a proper date.
#NUM! Error
- Invalid unit: Check that you are using valid DATEDIF units: Y, M, D, YM, MD, or YD.
- Invalid date: The date may be invalid (e.g., 31-Feb-2020). Verify the date is real.
Wrong Age (Off by 1)?
- Division method limitation: The /365.25 method can be off by 1 day near birthdays. Use DATEDIF instead.
- Date format confusion: DD/MM/YYYY vs MM/DD/YYYY can cause wrong calculations. Verify Excel is interpreting dates correctly.
DATEDIF Not in Autocomplete?
- This is normal: DATEDIF is undocumented and will not appear in autocomplete. Just type the full formula manually.
- Still works: Despite not being documented, DATEDIF works reliably in all Excel versions since 2000.
Frequently Asked Questions
What is the formula to calculate age in Excel?
The most accurate formula is =DATEDIF(birthdate,TODAY(),"Y") which calculates age in complete years. You can also use =INT(YEARFRAC(birthdate,TODAY(),1)) for similar results.
How do I calculate age in years, months, and days in Excel?
Use this combined formula: =DATEDIF(A2,TODAY(),"Y")&" years, "&DATEDIF(A2,TODAY(),"YM")&" months, "&DATEDIF(A2,TODAY(),"MD")&" days". This combines three DATEDIF functions with different unit arguments.
Why is DATEDIF not showing in Excel autocomplete?
DATEDIF is an undocumented function in Excel, so it does not appear in autocomplete. However, it works correctly when typed manually. Microsoft has never officially documented this function, but it has been available since Excel 2000.
How does Excel handle leap years when calculating age?
Both DATEDIF and YEARFRAC properly handle leap years. For people born on February 29th, Excel treats their birthday as March 1st in non-leap years for accurate age calculations.
Which age formula is most accurate in Excel?
DATEDIF is the most accurate for complete years. YEARFRAC with basis 1 (actual days) is also highly accurate. Simple division by 365.25 can have errors near birthdays.
How do I calculate age as of a specific date instead of today?
Replace TODAY() with your specific date. For example: =DATEDIF(A2,"2025-12-31","Y") calculates age as of December 31, 2025. You can also reference a cell containing the target date.
Can I calculate age in months or days only?
Yes, use "M" for total months: =DATEDIF(A2,TODAY(),"M") or "D" for total days: =DATEDIF(A2,TODAY(),"D"). These return complete months or days between the dates.
Summary
Calculating age in Excel is straightforward with the right formula:
- DATEDIF is the recommended method:
=DATEDIF(birthdate, TODAY(), "Y") - YEARFRAC is equally accurate:
=INT(YEARFRAC(birthdate, TODAY(), 1)) - Simple division works for quick estimates:
=INT((TODAY()-birthdate)/365.25) - For complete age, combine DATEDIF with "Y", "YM", and "MD" units.
Both DATEDIF and YEARFRAC handle leap years and edge cases correctly. Choose DATEDIF for most applications since it is the most intuitive and widely used for age calculations.
Create Excel Tutorial Videos
Need to train your team on Excel formulas? Deckoholic makes it easy to record screen tutorials with AI voiceover, zoom effects, and professional polish.
Try Deckoholic Free