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

Learning how to calc age in Excel is one of those small skills that quietly saves you hours across payroll sheets, school rosters, membership databases, and customer records. Whether you are tracking employee anniversaries or sorting a contact list by birth year, Excel gives you several reliable ways to turn a date of birth into an exact age in years, months, and even days. This guide walks through every method, from the simple subtraction trick to the powerful but hidden DATEDIF function that most people never discover on their own.

Age calculation seems like it should be obvious, yet it trips up beginners constantly. The reason is that Excel stores dates as serial numbers — January 1, 1900 is day 1, and every date after that is counted upward. When you subtract one date from another, Excel returns the number of days between them, not a neatly formatted age. Converting those raw days into a meaningful "34 years old" requires the right formula, and choosing the wrong approach leads to rounding errors that can be off by a full year.

The good news is that once you understand the logic, the formulas become second nature. You will rely mostly on three building blocks: the TODAY function, which always returns the current date; the DATEDIF function, which measures the difference between two dates in whatever unit you specify; and YEARFRAC, which returns a precise decimal age. Each has strengths and weaknesses, and knowing when to reach for which one is what separates a clean spreadsheet from a buggy one.

Throughout this tutorial we will use realistic examples with actual birth dates so you can follow along in your own workbook. We will assume a date of birth sits in cell B2 and that today's date is referenced dynamically, so your ages update automatically every time you open the file. That dynamic behavior is exactly what makes Excel so much better than a calculator — set the formula once and it stays accurate forever, recalculating the moment a new day begins.

We will also cover the practical edge cases that real data throws at you: leap years, people born on February 29, blank cells that should not show a misleading age of 120, and text that looks like a date but is not actually recognized as one. These problems are common in imported data, and we will show you how to spot and fix them before they corrupt your reports or skew an average age calculation across a large team.

By the end, you will be comfortable building an age column from scratch, formatting it cleanly, handling errors gracefully, and even displaying age in a friendly "34 years, 5 months, 12 days" format for certificates or HR documents. None of this requires VBA or add-ins — every technique uses standard worksheet functions available in Excel 2016, 2019, 2021, Microsoft 365, and Excel for the web, so your formulas will travel with your file wherever it goes.

The DATEDIF function is the workhorse for age calculations, and it is genuinely strange that Microsoft keeps it hidden — it does not appear in the formula autocomplete dropdown, and there is no entry for it in the modern function library. You simply have to know it exists and type it manually. The function takes three arguments: a start date, an end date, and a unit code wrapped in quotation marks. For age, the start date is the birth date, the end date is TODAY(), and the unit is usually "Y" for completed years.

To calculate someone's age in completed years, you would type =DATEDIF(B2,TODAY(),"Y"). If B2 holds March 15, 1990, and today is May 26, 2026, this returns 36 because the person has already had their birthday this year. The beauty of DATEDIF is that it counts only fully completed years — it will never tell you someone is 37 until the day they actually turn 37. That precision is exactly what payroll and benefits systems require, where being off by one year can change tax brackets or eligibility.

The unit code is where DATEDIF becomes flexible. "Y" gives whole years, "M" gives total completed months across the entire span, and "D" gives total days. The combination codes are more interesting: "YM" returns the leftover months after counting full years, "MD" returns the leftover days after months, and "YD" returns days ignoring years. These let you build a complete breakdown showing exactly how many years, months, and days old someone is, which is invaluable for medical charts and milestone certificates.

Here is the full breakdown formula you can paste directly: =DATEDIF(B2,TODAY(),"Y")&" years, "&DATEDIF(B2,TODAY(),"YM")&" months, "&DATEDIF(B2,TODAY(),"MD")&" days". The ampersand operator joins the three calculations with descriptive text, producing output like "36 years, 2 months, 11 days". Because every piece references TODAY(), the entire string updates itself each morning without any manual intervention, which makes it perfect for dashboards that stakeholders view daily.

One subtle point worth internalizing: DATEDIF requires the start date to be earlier than the end date, or it throws a #NUM! error. This becomes relevant when you have future dates by accident, or when a data-entry typo puts a birth year in the 2090s. We will cover trapping those errors later, but for now remember that the function is unforgiving about date order. If you ever see #NUM!, the very first thing to check is whether your two dates are reversed.

If you frequently look up ages from a master table, you might combine DATEDIF with vlookup excel techniques to pull a birth date from another sheet first, then calculate age in the same formula. This keeps your data normalized — one authoritative birth-date list — while letting any number of reports compute current age on demand. It is a pattern that scales beautifully from a 20-person team to a 20,000-record customer database without any change to the underlying logic.

Finally, always confirm that your birth-date cells are stored as real dates and not text. A quick test: select the cell and check whether the value right-aligns (a real date) or left-aligns (text). DATEDIF silently fails or returns garbage on text dates, so this five-second check prevents an entire afternoon of confused debugging when an imported CSV has dressed its dates up as ordinary strings.

Even with the correct formula, real-world data introduces problems that can make your age column look wrong or display alarming errors. The most common culprit is dates stored as text. When you import data from a CSV, a web export, or a database dump, dates frequently arrive as plain strings that merely look like dates. Excel left-aligns these and refuses to do math on them, so DATEDIF either errors out or returns a nonsensical figure. The fix is to convert them using the DATEVALUE function or the Text to Columns feature on the Data ribbon.

The second frequent issue is the #NUM! error, which appears when the start date is later than the end date. This usually means a birth date was typed with the wrong year — entering 2026 instead of 1962, for instance — or that you accidentally swapped the argument order inside DATEDIF. Scan for any age that shows as an error and verify the underlying date. A quick conditional formatting rule that highlights future dates in red will surface these typos instantly across thousands of rows.

Blank cells create their own trap. If a birth-date cell is empty, Excel treats it as the serial number zero, which corresponds to January 0, 1900. Your formula will then cheerfully report an age of 126 years, which is obviously wrong and will badly skew any average. The solution is to wrap your calculation in an IF test: =IF(B2="","",DATEDIF(B2,TODAY(),"Y")). This returns an empty string for missing data instead of an absurd age, keeping your summary statistics honest.

A more robust pattern combines both protections with IFERROR: =IFERROR(IF(B2="","",DATEDIF(B2,TODAY(),"Y")),"Check date"). Now a blank cell shows nothing, a valid date shows the correct age, and any problematic date shows a clear "Check date" message instead of a cryptic error code. This kind of defensive formula is the difference between a spreadsheet that survives messy real data and one that quietly produces wrong numbers nobody notices until it matters.

Leap-year birthdays deserve special mention. People born on February 29 only have a "real" birthday once every four years, but DATEDIF handles them sensibly by treating March 1 as the rollover in non-leap years, so their calculated age increments correctly. Simple subtraction with /365.25 is less reliable here and can occasionally be off by a day right around the birthday, which is one more reason to prefer DATEDIF for any data set where individual accuracy is important.

Formatting mistakes round out the list. Sometimes your age formula returns a date instead of a number — you see "1/5/1900" instead of "36". This happens because Excel inherited date formatting from the source cells. Simply select the result column, open the Number Format dropdown, and choose General or Number. The underlying value was always correct; only the display was confused. Cleaning up these presentation issues makes your work look polished and prevents colleagues from doubting otherwise accurate calculations.

When you are reshaping imported data so the dates parse correctly, the same skills you would use for how to create a drop down list in excel and other data-cleanup tasks come into play. Getting the source data clean first — consistent formats, no stray spaces, real date values — eliminates the vast majority of age-calculation headaches before you ever write a formula, and it is always time well spent on any data project.

Beyond the core formulas, a few structural habits make age-tracking spreadsheets far more pleasant to use and maintain. The first is freezing your header row so the column labels stay visible as you scroll through hundreds of records. Knowing how to freeze a row in excel takes seconds: click the View ribbon, choose Freeze Panes, and select Freeze Top Row. Now "Name," "Date of Birth," and "Age" remain anchored at the top no matter how far down you travel, which removes the constant guessing about which column is which.

The second habit is using structured tables. Select your data range and press Ctrl+T to convert it into an Excel Table. Tables automatically extend formulas to new rows, so when you add a new person their age calculates instantly without you copying anything down. They also give you clean banded rows, built-in filtering, and the ability to reference columns by name, turning =DATEDIF([@[Date of Birth]],TODAY(),"Y") into a self-documenting formula that anyone on your team can read and understand at a glance.

Dropdown lists keep categorical fields tidy when age data sits alongside other attributes. If you track an age bracket, a department, or a membership tier, learning how to create a drop down list in excel through Data Validation prevents typos and keeps your filters reliable. Consistent entries mean that when you later build a PivotTable to count members by age group, every category aggregates cleanly instead of splintering into near-duplicate spellings that ruin your summary counts.

Merging cells is tempting for titles but should be used sparingly in data areas. If you want a banner across the top of your roster, knowing how to merge cells in excel is handy for that single header row. Inside the actual data, though, merged cells break sorting, filtering, and copy-paste, so keep your age and date columns as plain single cells. Reserve merging strictly for cosmetic headers that sit above the data region, never within the rows you intend to analyze.

Sorting and filtering by age unlocks real insight once the column is clean. You can sort youngest to oldest to spot data-entry outliers, filter to everyone over 65 for benefits planning, or apply conditional formatting to flag upcoming milestone birthdays. Because your age column is a live formula tied to TODAY(), these views stay accurate every day — a filter for "turning 30 this month" genuinely surfaces the right people each time you open the file, with zero maintenance on your part.

For larger projects, consider separating raw data from reporting. Keep one sheet with names and birth dates as the single source of truth, then build a second sheet that pulls those values and calculates age. This mirrors how professionals approach how to merge cells in excel and combine data sets — keep inputs pristine and let outputs derive from them. When a birth date changes in one place, every report updates automatically, eliminating the version-control nightmares that plague copy-paste workflows.

Finally, document your formulas with a comment or a small notes cell. A future colleague — or you in six months — will appreciate a one-line reminder that the age column uses DATEDIF with TODAY() and is guarded against blanks. This tiny investment in clarity pays off every time someone inherits the workbook, and it signals the kind of careful, professional work that makes spreadsheets trustworthy rather than mysterious black boxes.

With the mechanics understood, here are the practical tips that turn an age column from functional into genuinely professional. Start every project by validating your source dates before writing a single formula. Select the date column, glance at the alignment, and run a quick =ISNUMBER(B2) check on a sample cell. If it returns FALSE, your dates are text and need conversion first. This thirty-second discipline prevents the single most common cause of broken age calculations and saves enormous frustration downstream.

When presenting age to non-technical readers, always favor the friendly format. A bare "36" works for analysis, but a certificate or a welcome letter reads far better as "36 years, 2 months, 11 days." Build a separate display column with the concatenated DATEDIF formula and keep the plain numeric column for any math, sorting, or averaging you need to perform. Mixing presentation text and numeric values in one column makes statistical functions like AVERAGE fail, so keep the two purposes physically separate.

For recurring reports, decide deliberately between a live age and a frozen age. TODAY() is perfect for a dashboard that should always reflect the present, but if you are producing a year-end snapshot dated December 31, hard-code that date with DATE(2025,12,31) so the figures never shift when someone reopens the file in March. Auditors and finance teams especially value frozen snapshots, because a report that silently changes its own numbers is impossible to reconcile against last quarter's version.

Build in error trapping from the start rather than bolting it on later. The combined pattern =IFERROR(IF(B2="","",DATEDIF(B2,TODAY(),"Y")),"Check date") should be your default template. Type it once, then fill it down. Establishing this habit means your spreadsheets gracefully handle the messy reality of incomplete data instead of erroring out in front of your boss during a presentation. Defensive formulas are a hallmark of experienced Excel users and cost almost nothing to implement.

Use conditional formatting to make age data actionable at a glance. A color scale across the age column instantly reveals your demographic distribution, while a rule that highlights ages under 18 in amber flags minors needing special handling. You can even create a rule that detects upcoming birthdays by combining MONTH and DAY functions, turning a static roster into a living reminder system that nudges you before each milestone arrives without any manual tracking.

Finally, keep practicing with realistic scenarios because muscle memory matters. Calculate your own age, a family member's, and a few historical figures to confirm the formulas behave as expected across centuries and leap years. Try the breakdown formula, then the YEARFRAC approach, and compare the results. The more you rehearse these patterns on varied data, the faster you will reach for the right one under pressure, and the more confident you will become tackling any date-based challenge Excel throws your way.

As you grow more comfortable, explore how age calculations feed into larger models — eligibility flags with IF, age-band lookups, and PivotTable summaries that count people per decade. These combinations are where Excel stops being a calculator and becomes a genuine analysis engine. Mastering the humble age formula is a surprisingly powerful first step toward that broader fluency, and it builds the date-handling intuition that underpins nearly every advanced spreadsheet technique you will ever learn.