How to Calculate Age in Excel: The Complete 2026 Guide to DATEDIF, YEARFRAC, and TODAY Formulas

Learning how to calculate age in Excel is one of those small skills that pays off again and again, whether you manage an HR roster, track customer birthdays, or build a school enrollment sheet. Excel does not have a single button labeled "age," but it gives you several reliable formulas that turn a date of birth into a precise number of years, months, and days. Once you understand the underlying logic, you can adapt these formulas to almost any reporting situation you encounter at work or at home.

The reason age math feels tricky is that calendars are irregular. Months have different lengths, leap years add a day every four years, and simple subtraction of one date from another gives you raw days rather than a friendly "34 years old." Excel stores every date as a serial number counting days from January 1, 1900, which is exactly why date arithmetic works at all. When you grasp this serial system, the formulas in this guide stop feeling like magic and start feeling like simple arithmetic you control.

In this guide we cover the three pillars of age calculation: the hidden DATEDIF function, the more modern YEARFRAC approach, and combinations using TODAY and INT. Each method has strengths, and knowing when to reach for which one separates a beginner from someone who builds clean, audit-proof spreadsheets. We also show how to handle edge cases like leap-year birthdays, future dates, and blank cells that would otherwise return ugly errors across your reports.

You do not need to be an advanced user to follow along. If you already know how to type a formula into a cell and press Enter, you have everything required. We assume Excel 2016 or later, including Microsoft 365 and Excel for the web, although the core functions have existed for decades and behave identically in older versions. Google Sheets users will find that most formulas here translate directly, with only minor syntax differences worth noting.

By the end, you will be able to display someone's age as a whole number of years, show a detailed "32 years, 4 months, 18 days" breakdown, and even calculate age automatically as today's date changes. We will also flag common mistakes, such as forgetting to lock a reference cell or mixing up text dates with real dates, so your numbers stay accurate when you share the file with colleagues who may not be spreadsheet experts.

Think of this article as both a tutorial and a reference. Read it start to finish the first time, then bookmark the formula tables and the FAQ for quick lookups later. Age calculation shows up everywhere once you start looking: payroll eligibility, insurance tiers, loyalty programs, medical records, and tenure reports. Master it now and you will reuse these patterns for years across every workbook you ever build.

The DATEDIF function is the workhorse of age calculation, and it has an interesting quirk: Microsoft does not list it in the function autocomplete menu, so you must type it from memory. Despite this, it works in every modern version of Excel. The syntax is DATEDIF(start_date, end_date, unit), where the start date is the date of birth, the end date is today or any reference date, and the unit is a text code telling Excel what to measure between those two points.

To get whole years, use "Y" as the unit. For example, if cell B2 holds a birth date of March 14, 1990, and cell C2 holds today's date, the formula =DATEDIF(B2, C2, "Y") returns the complete number of years that have passed. Excel correctly ignores partial years, so someone born in March who has not yet reached their birthday this year will still show the lower, accurate age rather than rounding up incorrectly, which is exactly what you want.

If you need more detail, DATEDIF supports six unit codes. "M" returns total months, "D" returns total days, "YM" returns leftover months after full years, "MD" returns leftover days after full months, and "YD" returns leftover days ignoring years. Combining these lets you build a sentence like "34 years, 2 months, 9 days" by concatenating three DATEDIF calls with text labels between them. This detailed format is popular for medical, legal, and certificate-generation spreadsheets.

Here is the classic detailed formula written in full: =DATEDIF(B2,TODAY(),"Y")&" years, "&DATEDIF(B2,TODAY(),"YM")&" months, "&DATEDIF(B2,TODAY(),"MD")&" days". The ampersand operator joins text and numbers together into one readable string. Notice how TODAY() replaces a fixed end-date cell, which means the result recalculates automatically. Each time you reopen the file or press F9, Excel refreshes the answer to match the real current date without any manual editing required from you.

A common beginner trap is reversing the date arguments. DATEDIF requires the earlier date first and the later date second; if you pass today's date as the start and the birth date as the end, Excel returns a #NUM! error because the result would be negative. Always confirm that your birth-date column comes first inside the parentheses. If your data could contain future dates, such as projected hire dates, add an IF check to catch those cases before they break your report.

Although the DATEDIF function feels niche, it shares DNA with everyday lookups like vlookup excel, where you also pass arguments in a strict order and Excel returns a single clean value. Treating each argument as a deliberate instruction rather than a guess is the mindset that makes all Excel functions click. Once DATEDIF becomes second nature, you will reach for it instinctively whenever any two dates need comparing, from project durations to subscription lengths.

One last tip for this section: format the result cell as General or Number, not as a Date. A frequent surprise is seeing a tiny date like "1/4/1900" instead of the number 34. That happens because Excel inherited date formatting from the birth-date cell. Highlight the cell, open Format Cells with Ctrl+1, and choose Number with zero decimal places. Your age will then display as a clean integer exactly as intended.

Even with the right formula, age calculations can go wrong in predictable ways, and recognizing these errors quickly saves hours of frustration. The most common problem is the #VALUE! error, which almost always means Excel is treating your birth date as text rather than a real date. This happens often when data is imported from a CSV file or pasted from a website, where dates arrive as strings that look correct but carry no underlying serial number for Excel to compute against.

To diagnose a text-date problem, click the suspect cell and look at the alignment. Real dates right-align by default, while text left-aligns unless someone changed it. You can also test with =ISNUMBER(B2); if it returns FALSE, the value is text. The fix is to convert it using the DATEVALUE function, the Text to Columns wizard, or by multiplying by one. Cleaning data first is non-negotiable, because no age formula can work on a value Excel does not recognize as a date.

The second frequent error is #NUM!, which appears when the start date is later than the end date. With age this usually means a future birth date slipped into your data, perhaps a typo turning 1995 into 2095. Guard against it with =IF(B2>TODAY(),"Check date",DATEDIF(B2,TODAY(),"Y")). This wrapper flags impossible entries instead of dumping a cryptic error code into your otherwise polished report, which makes the sheet far more trustworthy for colleagues reviewing it.

Blank cells cause their own headaches. An empty birth-date cell makes DATEDIF treat the blank as the date zero, January 0, 1900, producing a nonsensical age of 125 or more. The solution is an IF guard such as =IF(B2="","",DATEDIF(B2,TODAY(),"Y")). This returns an empty string when no birth date exists, keeping your column tidy. Combine it with the future-date check above to build one robust formula that handles both edge cases at once across an entire roster.

Leap-year birthdays, specifically February 29, occasionally raise questions. People wonder whether someone born on February 29, 2000, ages correctly in non-leap years. The good news is that DATEDIF handles this gracefully: it treats February 28 as the effective birthday in common years, matching how most legal systems calculate age. You generally do not need special handling, but it is worth knowing the logic so you can confidently answer when a detail-oriented colleague asks about it.

Regional date settings cause subtle bugs when files travel between countries. The United States uses month-day-year, while much of the world uses day-month-year. A date like 03/04/1990 means March 4 in the US and April 3 elsewhere. When sharing workbooks internationally, prefer the unambiguous DATE(year,month,day) function to build dates, or store dates in ISO format. This prevents an entire column of ages from quietly shifting by days or months without anyone noticing the discrepancy.

Finally, watch out for circular references and accidental formatting inheritance. If your age cell shows a date instead of a number, the cell simply inherited date formatting; fix it with Ctrl+1. If Excel warns about a circular reference, you likely pointed the formula at its own cell. Slow down, check each argument, and test a single row before filling thousands. Methodical verification beats firefighting a broken report after you have already shared it widely.

Once you have mastered single-cell age calculations, the real value comes from applying them across large datasets and combining them with other Excel features. Imagine an HR file with 5,000 employees: you want age, age bracket, and a flag for upcoming retirement all in one view. Start with your trusted =DATEDIF(B2,TODAY(),"Y") in column C, then build helper columns that reference it. This layered approach keeps each formula simple and easy to audit rather than cramming everything into one monstrous expression.

Age brackets are a perfect use for nested IF or the cleaner IFS function. Something like =IFS(C2<18,"Minor",C2<30,"18-29",C2<50,"30-49",TRUE,"50+") sorts everyone into readable groups. These labels then feed naturally into a PivotTable, where you can count how many people fall in each band in seconds. Pairing age formulas with PivotTables transforms a flat list into genuine insight that managers can act on during planning meetings and budget reviews.

Conditional formatting adds a visual layer that numbers alone cannot. Select your age column, choose a color-scale rule, and instantly see the youngest and oldest staff shaded differently. Or write a formula rule like =C2>=65 to highlight anyone at retirement age in bold red. This kind of dynamic styling, much like learning how to create a drop down list in excel for clean data entry, makes your spreadsheet feel like a polished application instead of a raw table of figures.

For dashboards, combine your age formulas with functions like AVERAGE, MEDIAN, and COUNTIFS. =AVERAGE(C2:C5001) gives the mean age of your workforce, while =COUNTIFS(C:C,">=50") counts everyone fifty and older. These summary statistics belong at the top of your sheet where decision-makers see them first. Because every formula chains back to TODAY, the entire dashboard refreshes itself automatically as time passes, with zero manual maintenance required from you each month.

When sharing these files, protect your hard work by locking the formula cells so colleagues cannot overwrite them accidentally. You can also keep headers visible while scrolling by learning how to freeze a row in excel, which keeps column labels like "Name" and "Age" pinned at the top. These usability touches matter enormously on long lists, where a reviewer scrolling to row 4,000 would otherwise lose track of which column shows what without constant scrolling back up.

Data validation rounds out a professional age sheet. Add a rule to the birth-date column that rejects future dates or dates before 1900, stopping bad data at the point of entry. Excel will show a custom error message you write, guiding the user to fix their input immediately. Catching mistakes during entry is far cheaper than discovering them later when an executive questions why the average age came out as 312 years in the quarterly report.

Finally, document your formulas. Add a small notes section or cell comments explaining what each helper column does and why you chose DATEDIF over YEARFRAC. Six months from now, you or a coworker will thank you. Spreadsheets outlive their creators' memories, and a one-line explanation prevents the dreaded moment when nobody dares touch a working formula because no one remembers how it functions or what it actually computes.

To finish, let us pull everything together into a practical workflow you can follow every time you need to calculate age in Excel, no matter the size or messiness of the source data. Begin by importing or pasting your dates into a clean column, then immediately verify they are real dates using ISNUMBER. Spending two minutes on data hygiene up front prevents the vast majority of errors that plague age calculations later in the process, and it builds a habit that serves every spreadsheet task you tackle.

Next, write your core age formula in the first data row only. Use =IF(B2="","",DATEDIF(B2,TODAY(),"Y")) so blanks stay empty and ages stay current. Press Enter and check the result against your own mental math for that one row. If a person born in 1990 shows roughly 35 in 2026, you know the formula is sound. Verifying a single row before filling down is the single most reliable quality check in all of spreadsheet work.

Once that row checks out, copy the formula down the entire column. Double-click the small fill handle at the bottom-right of the cell and Excel will copy it down to match your adjacent data automatically, which is far faster than dragging on long lists. Confirm that relative references shifted correctly: row 3 should reference B3, row 4 should reference B4, and so on, while any fixed reference cells remain locked in place.

If you need the detailed years-months-days format for certificates or medical records, swap in the concatenated DATEDIF formula from earlier in this guide. Keep both a simple integer column and a detailed text column if different stakeholders want different views. There is no penalty for having extra helper columns, and you can always hide them from the final printed report while keeping the underlying calculations intact and available for anyone who needs the granular detail.

Before sharing the file, do a final sweep. Sort the age column ascending and descending to surface any impossible values like negative numbers or ages over 120, which usually indicate a data-entry typo or a text date that slipped through. Apply number formatting so everything displays as clean integers. Add conditional formatting if it helps readers, and lock the formula cells so your careful work survives contact with other users who open the file.

Practice cements these skills faster than reading alone, so build a small test sheet with ten fictional birth dates spanning different decades, leap years, and edge cases. Calculate their ages three different ways using DATEDIF, YEARFRAC, and simple subtraction, then compare the results. Seeing where the methods agree and where they differ teaches you more in twenty minutes than an hour of passive study, and it builds the confidence to choose the right tool instinctively.

Age calculation is a gateway skill. The same date-arithmetic thinking powers tenure reports, subscription expirations, project timelines, warranty tracking, and countless financial models. Every time you reuse DATEDIF or TODAY, you reinforce a mental model that makes the next date problem trivial. Keep this guide bookmarked, run through the practice quizzes linked throughout, and within a week these formulas will feel as natural as typing SUM, ready whenever a date question lands on your desk.