How to Calculate Age in Excel – Step-by-Step Guide (With Examples)
Have you ever wondered how to calculate someone’s exact age in Excel — not just the years, but even the months and days?
If yes, then you’re in the right place! In this guide, you’ll learn how to calculate age in Excel easily using different formulas and examples perfect for beginners or anyone who wants to save time with Excel’s smart functions.
📅 Why Calculate Age in Excel?
Whether you’re managing student data, employee records, or even your own family birthdays, Excel makes it super simple to calculate age automatically.
You don’t need to count manually or use a calculator Excel does it instantly once you know the right formula!
✅ Easy to use
✅ Accurate and automatic
✅ Saves hours of manual work
🧠Method 1: Using the DATEDIF Function (Most Common Way)
The easiest and most accurate method to calculate age in Excel is by using the DATEDIF function.
🧩 Formula:
=DATEDIF(B2, TODAY(), "Y")
💡 Explanation:
-
B2 → The cell containing the date of birth.
-
TODAY() → Automatically picks the current date.
-
"Y" → Returns the number of full years between the two dates.
👉 Screenshot showing date of birth in cell B2 and the formula in cell C2 with the calculated age (e.g., 25).
(Alt text: Excel DATEDIF formula example for calculating age)
This formula gives you the age in years only.
For example:
If someone was born on 12/03/2000, the result will show 25 (as of 2025).
📆 Method 2: Calculate Age in Years, Months, and Days
If you want a more detailed breakdown of age including years, months, and days, you can use this extended version of DATEDIF:
🧩 Formula:
=DATEDIF(B2, TODAY(), "Y") & " Years, " & DATEDIF(B2, TODAY(), "YM") & " Months, " & DATEDIF(B2, TODAY(), "MD") & " Days"
💬 Output Example:
25 Years, 3 Months, 18 Days
👉 Screenshot of Excel displaying detailed age format: 25 Years, 3 Months, 18 Days.
(Alt text: DATEDIF detailed formula showing years, months, and days in Excel)
This formula is especially useful when you’re preparing HR records or school data and need a full age breakdown.
📊 Method 3: Using YEARFRAC Function (Quick & Simple)
The YEARFRAC function is another smart way to calculate age it gives the result in decimal form, which you can round off later.
🧩 Formula:
=INT(YEARFRAC(B2, TODAY()))
💡 Explanation:
-
YEARFRAC calculates the difference between two dates in years (including decimals).
-
INT converts it into a whole number (age in years).
👉 Excel screenshot showing YEARFRAC formula with an example.
(Alt text: YEARFRAC function used to calculate age in Excel)
This method is best when you just want age in years only quick and clean.
🧾 Method 4: Using Simple Date Subtraction (Alternative Way)
If you want to calculate the difference without DATEDIF, you can use a combination of Excel date functions.
🧩 Formula:
=ROUNDDOWN((TODAY()-B2)/365,0)
💬 Explanation:
-
TODAY()-B2 → Calculates total days lived.
-
Divide by 365 to convert into years.
-
ROUNDDOWN makes sure it doesn’t round up to the next year.
👉 Excel screenshot showing subtraction method.
(Alt text: Alternative way to calculate age by subtracting dates in Excel)
This formula is simple but less accurate because it doesn’t consider leap years.
🎯 Bonus Tip: Convert Age Automatically When You Change Dates
The best part about using TODAY() in your formula is that your Excel sheet updates automatically every day.
That means when a new year starts or a birthday passes, Excel recalculates the new age on its own.
No manual updates required! 💪
🧰 Quick Comparison of All Methods
| Method | Formula | Result Type | Accuracy |
|---|---|---|---|
| DATEDIF ("Y") | =DATEDIF(B2, TODAY(), "Y") |
Years Only | ✅ Very Accurate |
| DATEDIF (Y, YM, MD) | Full Formula | Years + Months + Days | ✅✅ Highly Accurate |
| YEARFRAC | =INT(YEARFRAC(B2, TODAY())) |
Years Only | ✅ Accurate |
| Subtraction | (TODAY()-B2)/365 |
Years (approx.) | ⚠️ Slightly Less Accurate |
