How to Calculate Age in Excel?

Excel is one of the most popular tools in the world, with its robust formulas and functions that can make complicated calculations. Calculating age is also one of its robust features that is mostly used by HR professionals tracking employee tenures, a data analyst working with demographic datasets, or someone who wants to stop doing the math manually. It is a great practical skill you should have.

Excel does not come with a built-in "AGE" function, but it offers several powerful date functions that make age calculation accurate, automated, and even dynamic. In this guide, you will learn every method, from the simplest one-liner to formulas that return exact age in years, months, and days.

Why Calculate Age in Excel?

Before jumping into the formulas, let's quickly look at why you would need age calculation in Excel. It helps you to understand the perfect use of the upcoming methods.

• HR & Payroll Teams: Calculate employee ages to manage retirement eligibility, benefits, or compliance.
• Healthcare Databases: Determine patient ages for treatment records or research.
• School & Education Records: Track student ages for admissions or grade eligibility.
• Survey & Research Data: Segment respondents by age group for analysis.
• Personal Use: Maintain family records, subscription renewals, or legal document tracking.

If you regularly work with data in Excel, learning to automate age calculation will save you significant time.

Also Read: Countif Function in Excel

What You Need Before You Start?

You can not directly implement a formula to find the age. To calculate age in Excel, you need:

1. A date of birth value stored in a cell (e.g., cell C3) in a proper Excel date format like DD/MM/YYYY or MM/DD/YYYY.
2. A reference date, either today's date (using TODAY()) or a fixed date.

Note: Make sure your date values are formatted as actual Excel dates, not text. If formulas return unexpected results or errors, right-click the cell → Format Cells → select "Date" format.

Read Also: Excel Keyboard Shortcuts

Method 1: The Basic Age Formula in Excel (Simple Division)

The simplest way to calculate age in Excel is to subtract the date of birth from today's date and divide by 365:

=(TODAY()-C3)/365

How it works:

• TODAY() returns the current date.
• Subtracting C3 (the birth date) gives the number of days between both dates.
• Dividing by 365 converts days into approximate years.

The problem: This formula returns a decimal (e.g., 35.38 years). To display only the completed years, wrap it with INT():

=INT((TODAY()-C3)/365)

Drawback: Dividing by a flat 365 does not account for leap years, which can cause minor inaccuracies over time. This method is fine for rough estimates but not recommended for legal or official documentation.

Related Source: Excel Interview Questions and Answers

Method 2: Using YEARFRAC and INT for Greater Accuracy

The YEARFRAC function is designed to return the fraction of a year between two dates. When combined with INT, it gives you a clean, more accurate age in completed years.

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

Breaking it down:

• C3, the start date (date of birth)
• TODAY(), the end date (current date)
• 1, the basis parameter, which tells Excel to use actual calendar days (actual/actual method)
• INT(), strips the decimal to return only completed years

Why use basis=1? Without specifying the basis, YEARFRAC defaults to treating every month as 30 days, which is less accurate. Setting it to 1 means Excel uses the real number of days in each month and year, including leap years.

This method is widely considered the best balance between simplicity and accuracy for most use cases.

Also Read: Excel ROUND(): A Quick Guide to Rounding in Excel

Method 3: Using DATEDIF to Calculate Age in Excel (Most Reliable)

The DATEDIF function (short for "Date Difference") is purpose-built for calculating the interval between two dates. It is particularly precise for age calculations because it counts only fully completed periods.

Basic DATEDIF formula:

• =DATEDIF(C3, TODAY(), "Y")
• C3, the birth date
• TODAY(), the current date
• "Y", returns the number of complete years elapsed

Important note: DATEDIF is a legacy function in Excel, it works perfectly but does not appear in Excel's autocomplete suggestions. You need to type it manually. Do not let that discourage you; it is one of the most reliable age formulas available.

Related Source: How to Use Power Query in Excel?

Method 4: Calculate Exact Age in Years, Months, and Days

When you need more than just the year, for example, in medical or legal contexts, you can combine three DATEDIF calls to get the exact age breakdown:

For years:

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

For months (beyond the last completed year):

=DATEDIF(C3, TODAY(), "YM")

For days (beyond the last completed month):

=DATEDIF(C3, TODAY(), "MD")

Combined formula in a single cell:

=DATEDIF(C3,TODAY(),"Y")&" Years, "&DATEDIF(C3,TODAY(),"YM")&" Months, "&DATEDIF(C3,TODAY(),"MD")&" Days"

Example output: 26 Years, 5 Months, 22 Days

Bonus, hide zero values for cleaner output:

=IF(DATEDIF(C3,TODAY(),"Y")=0,"",DATEDIF(C3,TODAY(),"Y")&" Years, ")&IF(DATEDIF(C3,TODAY(),"YM")=0,"",DATEDIF(C3,TODAY(),"YM")&" Months, ")&IF(DATEDIF(C3,TODAY(),"MD")=0,"",DATEDIF(C3,TODAY(),"MD")&" Days")

This version skips displaying "0 Months" or "0 Days" when they are irrelevant.

Read Also: How to Create Dashboard in Excel? A step-by-step Guide

Method 5: Calculate Age on a Specific Date (Not Today)

Sometimes you need to calculate age at a specific historical or future date, for example, a candidate's age on the date of an exam, or an employee's age on a contract start date.

Simply replace TODAY() with the DATE() function:

=DATEDIF(C3, DATE(2025, 12, 31), "Y")

This formula tells you how old the person was (or will be) on December 31, 2025.

For a full breakdown on a specific date:

=DATEDIF(C3,DATE(2025,12,31),"Y")&" Years, "&DATEDIF(C3,DATE(2025,12,31),"YM")&" Months, "&DATEDIF(C3,DATE(2025,12,31),"MD")&" Days"

This approach is especially useful in HR planning, insurance policy management, or historical data analysis.

Related Source: How to Remove Spaces in Excel?

Method 6: Calculate Age in a Specific Year Only

If you only have a birth year and want to know someone's age in a particular year (without needing exact months), use:

=DATEDIF(C3, DATE(2030, 12, 31), "Y")

For mid-year calculations:

=DATEDIF(C3, DATE(2030, 6, 30), "Y")

This is helpful in long-term workforce planning, pension forecasting, or demographic projections.

Read Also: How to Compare Two Columns in Excel?

Method 7: Find the Date When Someone Reaches a Specific Age

Need to know when an employee will turn 60 for retirement planning? Use the DATE function with YEAR, MONTH, and DAY:

=DATE(YEAR(C3)+60, MONTH(C3), DAY(C3))

This returns the exact future date when the person reaches that age. You can extend this for more complex eligibility checks:

=DATE(YEAR(C3)+30, MONTH(C3)+6, DAY(C3))

This finds the date when someone will be exactly 30 years and 6 months old.

Related Source: How to Remove Blank Rows in Excel?

Bonus: Highlight Ages Using Conditional Formatting

Once you have age values calculated, you can visually highlight them by age group using Conditional Formatting, useful for quick data review in HR databases or student records.

Steps:

1. Select your age data range.
2. Go to Home → Conditional Formatting → New Rule.
3. Choose "Use a formula to determine which cells to format".
4. Enter the relevant formula and pick a fill colour.

This visual layer makes large spreadsheets much easier to read at a glance.

Quick Comparison: Which Formula Should You Use?

Read Also: Top 120 Excel Multiple Choice Questions (MCQs) For 2026

Common Mistakes to Avoid

Beginners often make multiple mistakes while using these formulas. The following are some of them. Try to avoid these mistakes:

1. Dates stored as text: If your birth dates are stored as text (left-aligned in the cell), Excel cannot calculate with them. Use the DATEVALUE function or reformat the cells.

2. Dividing by 365 for large datasets: For long timeframes or leap-year-heavy data, this causes cumulative inaccuracies. Switch to DATEDIF or YEARFRAC with basis=1.

3. Forgetting to specify basis in YEARFRAC: Without it, the function defaults to a 30/360 method, not the real calendar. Always include the 1 as the third argument.

4. Not locking reference dates with $: If you use a fixed date in a formula and want to copy it down a column, use an absolute cell reference (e.g., $B$1) instead of typing the date directly in each formula.

5. Expecting DATEDIF in autocomplete: DATEDIF does not appear in Excel's formula suggestions. Just type it manually, it works perfectly.

Related Excel Skills Worth Learning

Age calculation is just one slice of what Excel's date and time functions can do. Once you are comfortable with this, explore these related capabilities:

• Calculating the number of days between dates, useful for project timelines and contract durations
• Working with NETWORKDAYS, to count working days excluding weekends and holidays
• Using IF with date functions, to flag upcoming birthdays, expired dates, or eligibility windows
• Power Query for bulk age calculations, ideal for large HR or research databases that refresh regularly

If you are serious about building professional-grade Excel skills, check out these resources from IGMGuru:

• Microsoft Advanced Excel Training, deep dive into formulas, pivot tables, and data analysis
• Power BI Training, take your data visualization to the next level
• Data Science with Python Training, for automating data tasks beyond Excel's scope
• Excel Formulas Cheat Sheet, a quick-reference guide for all key Excel formulas
• Excel Keyboard Shortcuts, speed up your Excel workflow with essential shortcut keys

Conclusion

Calculating age in Excel is straightforward once you pick the right formula for your use case. For quick estimates, the basic division formula works. For accuracy in professional settings, DATEDIF or YEARFRAC with basis=1 are the right tools. And for detailed breakdowns in years, months, and days, combining multiple DATEDIF calls gives you everything you need.

Master these formulas, and you will handle any age-related calculation in Excel confidently, whether it is a single birthday or a dataset with thousands of records.

Frequently Asked Questions (FAQ)

Q1. Does Excel have a built-in age function?

No. Excel does not have a dedicated AGE function. However, functions like DATEDIF, YEARFRAC, and INT, when used correctly, give accurate and reliable age results.

Q2. Which is the most accurate formula to calculate age in Excel?

DATEDIF with the "Y" unit is generally the most accurate for calculating completed years, as it accounts for actual calendar differences including leap years and varying month lengths.

Q3. Why is DATEDIF not showing in Excel's autocomplete?

DATEDIF is a legacy compatibility function in Excel. It works perfectly fine but is intentionally hidden from the formula autocomplete list. You must type it manually.

Q4. Can I calculate age in Excel without using TODAY()?

Yes. Replace TODAY() with a specific date using DATE(year, month, day). For example: =DATEDIF(C3, DATE(2025, 1, 1), "Y").

Q5. What happens if the birth date is on February 29 (leap day)?

Both DATEDIF and YEARFRAC handle leap-day birthdays correctly. In non-leap years, Excel treats the birthday as falling on March 1st for calculation purposes.

Q6. How do I calculate age in months or days instead of years?

Use DATEDIF with different unit codes: "M" for total months, "D" for total days, "YM" for months past the last full year, and "MD" for days past the last full month.