How to Calculate Age in Excel (DATEDIF & YEARFRAC) + Free Template

Calculating age in Excel is one of the most common spreadsheet tasks—whether you're managing employee records, tracking customer birthdays, or building a school database. This comprehensive guide covers every method you'll need, from simple year calculations to precise age breakdowns in years, months, and days.

Table of Contents

1. Before You Start: Date Formatting Basics

2. Method 1: DATEDIF – Age in Complete Years

3. Method 2: Age in Years, Months, and Days

4. Method 3: Calculate Age as of a Specific Date

5. Method 4: YEARFRAC – Age with Decimals

6. Method 5: Calculate Age for Multiple Rows

7. Bonus: Days Until Next Birthday

8. Method Comparison Table

9. Common Problems and Solutions

10. Excel vs Google Sheets

11. Online Alternative

12. FAQ

Before You Start: Date Formatting Basics

Before diving into formulas, ensure your dates are properly formatted. Excel distinguishes between actual dates and text that looks like dates—and this difference matters.

How to check if a cell contains a real date:

1. Click on the cell with your date

2. Look at the formula bar—if it shows a number (like 45678), it's a real date

3. If it shows text (like "01/15/1990"), Excel treats it as a string

Converting text to dates:

If your date is stored as text, convert it first:

=DATEVALUE(A2)

Then format the result cell as a Date (Right-click → Format Cells → Date).

Important: Different locales interpret date formats differently. "01/02/2000" could mean January 2nd (US) or February 1st (Europe). Always verify your dates are parsing correctly.

Method 1: DATEDIF – Age in Complete Years

The DATEDIF function is the most straightforward way to calculate age in Excel. It returns the number of complete years between two dates.

Formula:

=DATEDIF(A2, TODAY(), "Y")

Where:

• A2 = cell containing the date of birth

• TODAY() = current date (updates automatically)

• "Y" = return complete years

Example:

Result (as of January 2026):

Note: DATEDIF is a "hidden" function in Excel—it won't appear in autocomplete, but it works perfectly. Just type it manually.

Method 2: Age in Years, Months, and Days

For situations requiring precise age (medical records, legal documents, HR systems), you'll want the complete breakdown.

Individual formulas:

Years:  =DATEDIF(A2, TODAY(), "Y")
Months: =DATEDIF(A2, TODAY(), "YM")
Days:   =DATEDIF(A2, TODAY(), "MD")

The second parameter codes:

• "Y" = total complete years

• "YM" = remaining months after complete years

• "MD" = remaining days after complete months

All-in-one formula (single cell):

=DATEDIF(A2,TODAY(),"Y") & " years, " & DATEDIF(A2,TODAY(),"YM") & " months, " & DATEDIF(A2,TODAY(),"MD") & " days"

Example output: "35 years, 10 months, 12 days"

Professional formatting with labels:

For cleaner output, you can add conditional formatting:

=DATEDIF(A2,TODAY(),"Y") & IF(DATEDIF(A2,TODAY(),"Y")=1," year, "," years, ") & DATEDIF(A2,TODAY(),"YM") & IF(DATEDIF(A2,TODAY(),"YM")=1," month, "," months, ") & DATEDIF(A2,TODAY(),"MD") & IF(DATEDIF(A2,TODAY(),"MD")=1," day"," days")

This handles singular/plural correctly (e.g., "1 year" vs "2 years").

Method 3: Calculate Age as of a Specific Date

Sometimes you need to calculate age on a specific date rather than today—for example, age at time of event, age at enrollment, or age at a historical date.

Formula:

=DATEDIF(A2, B2, "Y")

Where:

• A2 = date of birth

• B2 = reference date (event date, enrollment date, etc.)

Example: Employee age at hire date

Full breakdown as of specific date:

=DATEDIF(A2,B2,"Y") & " years, " & DATEDIF(A2,B2,"YM") & " months, " & DATEDIF(A2,B2,"MD") & " days"

Method 4: YEARFRAC – Age with Decimals

When you need age as a decimal number (common in financial calculations, actuarial work, or scientific applications), use YEARFRAC.

Formula:

=YEARFRAC(A2, TODAY(), 1)

Example output: 35.87 (meaning 35 years and about 10.5 months)

Basis options (third parameter):

Recommendation: Use 1 (actual/actual) for age calculations—it's the most accurate.

To get just the integer part:

=INT(YEARFRAC(A2, TODAY(), 1))

To get age rounded to one decimal:

=ROUND(YEARFRAC(A2, TODAY(), 1), 1)

Method 5: Calculate Age for Multiple Rows

When working with lists of people (employees, students, customers), you need efficient ways to calculate age for everyone at once.

Step 1: Set up your data

Step 2: Enter formula in first row

In cell C2, enter:

=DATEDIF(B2, TODAY(), "Y")

Step 3: Copy down

• Select C2

• Double-click the small square in the bottom-right corner (fill handle)

• Excel automatically copies the formula to all rows with data

Using Tables for automatic expansion:

1. Select your data range (A1:C5)

2. Press Ctrl+T to create a Table

3. Enter formula in first data row of Age column

4. Excel automatically applies it to all rows—and new rows you add later

Age categories formula:

To group ages into categories (useful for demographics analysis):

=IF(DATEDIF(B2,TODAY(),"Y")<18,"Minor",IF(DATEDIF(B2,TODAY(),"Y")<65,"Adult","Senior"))

Bonus: Days Until Next Birthday

A popular use case: birthday reminders or "days until birthday" calculations.

Formula:

=DATE(YEAR(TODAY()) + (DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))<TODAY()), MONTH(A2), DAY(A2)) - TODAY()

How it works:

1. Constructs this year's birthday from the birth date

2. If birthday already passed this year, adds 1 to get next year

3. Subtracts today's date to get remaining days

Example output: 47 (days until birthday)

Alternative: Next birthday date

To show the actual date of next birthday:

=DATE(YEAR(TODAY()) + (DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))<TODAY()), MONTH(A2), DAY(A2))

Method Comparison Table

Recommendation:

• For most cases, use DATEDIF with "Y" parameter

• For exact breakdowns, use DATEDIF with all three parameters

• For decimal ages, use YEARFRAC with basis 1

Common Problems and Solutions

Problem: DATEDIF doesn't appear in autocomplete

Solution: This is normal. DATEDIF is a legacy function that Microsoft never officially documented. Just type it manually—it works in all Excel versions from 2007 onwards.

Problem: #VALUE! error

Causes:

• One or both cells contain text instead of dates

• Empty cells in the formula range

Solutions:

1. Convert text to date: =DATEVALUE(A2)

2. Check for empty cells: =IF(A2="", "", DATEDIF(A2, TODAY(), "Y"))

Problem: Negative result or #NUM! error

Cause: The end date is before the start date (e.g., future birth date, or dates in wrong order)

Solutions:

1. Swap the arguments: =DATEDIF(B2, A2, "Y") instead of =DATEDIF(A2, B2, "Y")

2. Add validation: =IF(A2>TODAY(), "Future date", DATEDIF(A2, TODAY(), "Y"))

Problem: Formula shows semicolons instead of commas

Cause: Your Excel uses European locale settings

Solution: Replace commas with semicolons:

=DATEDIF(A2; TODAY(); "Y")

Problem: Age is off by one year

Cause: Usually a date format interpretation issue

Solution:

1. Verify the date is correct in the formula bar

2. Re-enter dates in ISO format (YYYY-MM-DD)

3. Use DATEVALUE to explicitly parse the date

Problem: Formula works but shows as text

Solution: Remove any leading apostrophe from the cell, or re-enter the formula without spaces before the equals sign.

Excel vs Google Sheets

Both Excel and Google Sheets support DATEDIF, but there are subtle differences:

Google Sheets specific:

• DATEDIF appears in autocomplete (unlike Excel)

• Date parsing is generally more forgiving

• Same formula syntax works in most cases

Tip: For maximum compatibility between both platforms, always use:

• ISO date format (YYYY-MM-DD)

• Explicit date functions rather than text dates

• DATEDIF for age calculations (works identically in both)

Online Alternative

Prefer not to use spreadsheets? Try our free Age Calculator Online to instantly calculate exact age in years, months, weeks, and days. No formulas required—just enter a birth date and get precise results.

The online calculator also provides:

• Age in multiple units (years, months, weeks, days)

• Next birthday countdown

• Day of the week you were born

• Total days lived

FAQ

Is DATEDIF accurate for leap years?

Yes. When your cells contain properly formatted dates, Excel automatically accounts for leap years and varying month lengths. The DATEDIF function correctly handles February 29th birthdays and calculates age accurately regardless of leap year considerations.

Which method is best: DATEDIF or YEARFRAC?

Use DATEDIF when you need clean, whole numbers (years, months, days)—this is the standard for most business and personal use cases. Use YEARFRAC when you specifically need decimal values, such as in financial calculations, actuarial tables, or scientific applications where fractional years matter.

Can I calculate age in months only?

Yes. Use the "M" parameter in DATEDIF:

=DATEDIF(A2, TODAY(), "M")

This returns the total number of complete months between the birth date and today.

Why is DATEDIF not showing in Excel autocomplete?

DATEDIF is a compatibility function that Microsoft inherited from Lotus 1-2-3. It was never officially documented in Excel's help system, so it doesn't appear in autocomplete or function wizard. However, it's fully functional and widely used. Just type it manually.

How do I calculate age between two specific dates?

Replace TODAY() with a cell reference containing your target date:

=DATEDIF(A2, B2, "Y")

Where A2 is the birth date and B2 is the date you want to calculate age as of.

Does this work in older Excel versions?

Yes. DATEDIF has been available since Excel 97. The formulas in this guide work in Excel 2007, 2010, 2013, 2016, 2019, 2021, and Microsoft 365.

How can I calculate the age of multiple people at once?

Enter the DATEDIF formula in the first row of your age column, then drag the fill handle down to copy it to all rows. Alternatively, convert your data to an Excel Table (Ctrl+T) and the formula will automatically apply to all rows, including new ones you add later.

Can I calculate age and display it with custom formatting?

Yes. Combine DATEDIF with TEXT functions and concatenation:

=DATEDIF(A2,TODAY(),"Y") & "y " & DATEDIF(A2,TODAY(),"YM") & "m " & DATEDIF(A2,TODAY(),"MD") & "d"

Output: "35y 10m 12d"