Startseite » Excel EN » How to Calculate Age in Excel Using Formulas

How to Calculate Age in Excel Using Formulas

If you’re wondering how to calculate age in Excel, the good news is — it’s easier than you might think! 🎉 Whether you’re organizing birthdays 🎂, HR records 📁, or student ages 📊, Excel offers fast and reliable formulas to get precise age calculations. In this blog post, you’ll learn multiple methods to calculate age in years, months, or days — plus some handy shortcuts for Windows, macOS, and Ubuntu users. 🧠

Using the DATEDIF Function to Calculate Age

The most straightforward and Excel-friendly way to calculate age is with the DATEDIF function, which is designed to find the difference between two dates.

Formula:

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

Explanation:

  • B2 is the cell with the date of birth.
  • TODAY() returns the current date.
  • "Y" tells Excel to return the number of full years.

✅ This method works perfectly to calculate someone’s age in years.

How to Calculate Age with Years, Months, and Days

If you want to get more precise age results, like “25 years, 4 months, and 12 days”, you can extend DATEDIF.

Formula:

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

This is especially helpful for applications like:

  • Medical records 📄
  • Employee files 🧑‍💼
  • School admissions 📘

Calculating Age Based on a Specific Date (Not Today)

Sometimes, you need to calculate age as of a specific date, not the current day.

Formula:

=DATEDIF(B2, C2, "Y")

Where:

  • B2 = date of birth
  • C2 = reference date (e.g., 01/01/2025)

Calculate Age with INT and YEARFRAC Functions

Another great method uses YEARFRAC, which returns age as a decimal.

Formula:

=INT(YEARFRAC(B2, TODAY()))
  • It gives you the full number of years, rounding down.
  • It’s great when you’re comfortable without using DATEDIF.

How to Format the Birth Date in Excel

To avoid formula errors, always make sure your date of birth cells are formatted as Date.

Steps:

  1. Select the cell(s)
  2. Go to Home > Number Format > Short Date
  3. Or use Ctrl + 1 to open Format Cells

Keyboard Shortcuts for Excel Age Calculation

🚀 Boost your productivity with these quick shortcuts when working with age calculations:

Windows:

  • Ctrl + ; → Insert current date
  • Ctrl + Shift + # → Format as date
  • Ctrl + F1 → Show/hide ribbon

macOS:

  • Command + ; → Insert today’s date
  • Command + 1 → Format cells dialog
  • Command + Shift + K → Open functions browser

Ubuntu (via LibreOffice Calc or Excel Online):

  • Ctrl + ; → Insert date
  • Ctrl + 1 → Format cells
  • Ctrl + Shift + F9 → Recalculate formulas

Automate Age Calculations in Excel Table

  1. Make your dataset into a table using Ctrl + T
  2. Add a new column called “Age”
  3. Apply the formula =DATEDIF([@DOB], TODAY(), "Y")

🔁 Now your table auto-updates each time you add a new row!

Common Errors When Calculating Age in Excel

#NUM! Error – This happens when the start date is greater than the end date.
Wrong age – Double-check that cells are in Date format and not Text.
Dates not recognized – Use Excel’s built-in date formatting and avoid typing dates manually in quotes.

FAQs About Calculating Age in Excel

Can I calculate age in months only?
Yes! Use =DATEDIF(B2, TODAY(), "M") to get the total months difference.

Is DATEDIF available in all Excel versions?
Yes, though it’s a hidden function. It’s supported in most Excel versions from 2007 onwards.

Can I use age calculation in Google Sheets?
Absolutely! The same DATEDIF formula works in Google Sheets.

What if I want to calculate age dynamically every year?
Use TODAY() in your formula to ensure it always calculates based on the current date.

Can I highlight people over 18 using conditional formatting?
Yes. Use a rule like =DATEDIF(B2, TODAY(), "Y")>=18.

Does Excel automatically update age each year?
Yes — if you use TODAY() in your formula, it updates dynamically.

Conclusion: Excel Age Calculation Made Easy 📅

Now that you know how to calculate age in Excel, you can confidently handle date-based data like a pro. Whether you’re keeping track of employee records, student ages, or personal files, these Excel formulas and shortcuts will save you loads of time! ⏱️

Make use of DATEDIF, YEARFRAC, and table automation to keep your spreadsheets smart and efficient. No more manual calculations or mistakes — just accurate age results with a click. 😄

Complete List of Windows Keyboard Shortcuts

If you need help for Windows, you can find a whole list of all keyboard shortcuts here.

https://keyboard-shortcuts.org/

Advertisment