Age looks like the simplest thing a spreadsheet should be able to figure out. You have a birth date in one column, today’s date is built into the program, subtract one from the other — done, right? In practice, that subtraction returns a decimal year count that drifts off by a day or two every leap year, and HR keeps emailing you about the 39-year-old who turned 40 last Tuesday but still shows as 39 on the staffing roster.

This guide walks through the real way to calculate age in Excel — the formulas that work, the ones that almost work, and the format gotchas that bite people who paste data from another system. We’ll cover DATEDIF (a function Microsoft doesn’t advertise but still ships), YEARFRAC, the divide-by-365.25 shortcut, and a handful of tricks for handling dd/mm/yyyy data, age at a specific historical date, and reverse lookups where you have an age and need an approximate birth date.

If you’ve been pasting =(TODAY()-B2)/365 into your sheets for years and shrugging at the rounding errors, you’re going to like what DATEDIF does. And if you’ve fought with European date formats — the rest of the world writes day first, after all — we’ll fix that too with a small DATE rebuild that doesn’t depend on regional settings.

One note before the formulas. Everything here works in Excel 2016, 2019, 2021, Microsoft 365, and Excel for Mac. The behavior is identical in Google Sheets except for one quirk (the DATEDIF “MD” argument can return negative values in edge cases), and LibreOffice Calc follows the same syntax. You don’t need the newest version of anything to make this work.

Here’s the headline formula. Put a birth date in cell B2 and drop this into C2:

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

That returns a whole-number age in years. It counts completed years, the way a human counts age — if your birthday is December 15 and today is December 14, you’re still showing as the younger number until midnight. No fractional weirdness, no leap-year drift, no off-by-one on Feb 29 birthdays. The function understands calendar math.

The catch — and this is the only reason you’ve never seen DATEDIF in autocomplete — is that Microsoft inherited it from Lotus 1-2-3 in the 1990s and never officially documented it. Type =DATEDIF( into the formula bar and Excel will not pop up a function tooltip. It works, but Microsoft pretends it doesn’t. The function ships in every version, has shipped for thirty years, and there’s no roadmap to remove it — but its undocumented status is why so many tutorials default to clumsier alternatives.

DATEDIF takes three arguments: start date, end date, unit. The unit string controls what gets returned. “Y” gives completed years. “M” gives completed months. “D” gives total days. There are also three composite units — “YM” (months ignoring years), “MD” (days ignoring months and years), and “YD” (days ignoring years) — that let you build the “X years, Y months, Z days” phrasing people actually want on certificates and HR letters.

Let’s build the “years, months, days” version because it’s the next-most-asked question. The trick is to call DATEDIF three times and stitch the results together. In cell C2, with B2 holding the date of birth:

=DATEDIF(B2,TODAY(),"Y")&" years, "&DATEDIF(B2,TODAY(),"YM")&" months, "&DATEDIF(B2,TODAY(),"MD")&" days"

That returns something like 34 years, 7 months, 12 days. The ampersand (&) is Excel’s concatenation operator — it glues strings together. You can also use the CONCAT or TEXTJOIN functions for the same effect; pick whichever style your team reads more easily.

One small refinement: hide the “0 months” or “0 days” in the rare case the math comes out clean. Wrap each segment in an IF that returns an empty string when the value is zero. It’s extra typing, but on a printed birthday card or HR letter, “34 years, 0 months, 0 days” looks robotic. Or, more pragmatically, just leave it — nobody ever turns exactly N years to the second, and the readable defaults are fine 364 days a year.

Now, what if you need decimal years — for actuarial work, age-on-policy-date calculations, or anything where partial years matter? That’s where YEARFRAC earns its keep. The next section covers it alongside the two other “close enough” approaches you’ll see in older spreadsheets.

So which one should you actually use? For most users, the answer is still DATEDIF with the “Y” unit. It’s shorter, faster to read, and ports between Excel and Google Sheets without changes. YEARFRAC is the right choice when you need the decimal — an underwriter computing age-nearest-birthday for a life insurance quote, or a researcher computing age-at-event with sub-year resolution.

The 365.25 trick survives because it’s short and people remember it. But it’s genuinely wrong for some birthdays. Take someone born on February 29, 2000. As of March 1, 2024, DATEDIF says they’re 24 (correct — they just celebrated their first “real” birthday since 2020). The 365.25 formula sometimes returns 23, sometimes 24, depending on which leap year was most recent in the calendar window. If you maintain an HR roster, do not use 365.25. The bug surfaces every four years and always at the worst time.

Date format is the next landmine. If your source data came from a European system, the United Kingdom, India, or anywhere else that writes day first, Excel may have already misread the values. A birth date written 03/04/1990 is March 4 in the US and April 3 elsewhere. Excel uses your regional setting to interpret the import — and if it guesses wrong, every age calculation downstream is off by months. Always sanity-check a few rows after a bulk paste.

Once the dates are clean, the formulas are deterministic. The hard part is almost always the input layer — getting Excel to recognize the values as dates rather than text, and getting the locale right. A common workflow trick is to keep a tiny “date diagnostic” cell at the top of any sheet you import data into. Drop =ISNUMBER(B2) into D1 and reference your first data row. If it returns TRUE, B2 is a real date and your DATEDIF will work. FALSE means text, and you need a conversion step before any math.

For data that arrives from web scrapes, CSV exports, or older databases, the safest pattern is to (1) import everything as text, (2) run a single DATEVALUE or DATE rebuild into a new column, and (3) point your age formulas at the new column. Don’t fight Excel’s auto-conversion — just bypass it by importing as text first and parsing manually. The extra column costs nothing and makes the transformation auditable. When something goes wrong six months from now, you’ll be able to see exactly which transformation was applied and where.

One more gotcha worth flagging: the leading-zero problem. Some payroll systems export dates as 05/03/1990 with the leading zeros preserved, while others drop them and you get 5/3/1990. Excel handles both fine when interpreting actual dates, but if you’re parsing text with LEFT, MID, and RIGHT, the substring positions shift. A more robust approach for messy text dates: use Power Query (Data > Get Data > From Text) and let it infer types, or use the TEXTSPLIT function (Microsoft 365) to split on slashes and rebuild with DATE.

The next tricky case: age at a specific date, not today. Eligibility cutoffs, policy effective dates, school-grade placement — lots of real workflows need to know how old someone was on a date in the past or future. Easy fix, and worth knowing because the same pattern handles all of these.

Some readers land on this page wanting the reverse: given an age, what’s the approximate birth year? The simplest form is =YEAR(TODAY())-A2 where A2 holds the age. That gives you the year someone was born if they’ve already had their birthday this calendar year. For a tighter approximation that gives a date range, build a two-cell output: earliest possible birthday (today’s month/day, minus the age) and latest possible birthday (one day before that, age plus one year).

Reverse age math is genuinely approximate — you can’t recover the exact birth date from a whole-year age without more information. But for genealogy projects, demographic estimates, or working through a census record where only age is recorded, the year-of-birth shortcut gets you most of the way home. Pair it with any other temporal clue (marriage records, military service dates, school enrollment) to narrow down.

For people building dashboards or HR reports, the next step is usually grouping ages into bands — under 25, 25–34, 35–44, etc. That’s where INT, FLOOR, and lookup tables join the party. A compact way to assign a band: =LOOKUP(DATEDIF(B2,TODAY(),"Y"), {0,25,35,45,55,65}, {"Under 25","25-34","35-44","45-54","55-64","65+"}). The two array constants set the band thresholds and labels; tweak as needed for your project.

That checklist looks fussy, but most of those steps become muscle memory inside a week. The two that trip people up — even experienced Excel users — are format mismatches and the result cell being formatted as a date. The second one is wild: a formula returns the number 34, but the cell’s number format is Date, so Excel reads 34 as “day 34 from the epoch” and displays February 3, 1900. Highlight the cell, press Ctrl+Shift+~ to force General format, and the real age pops back. Took me an embarrassingly long time the first time I hit it.

The other underrated tip: when you build a long roster of ages, use a calculated column in a Table (Ctrl+T) rather than a plain range. Tables automatically extend formulas to new rows, give you structured references like [@[Date of Birth]] instead of B2, and don’t silently drop the formula when someone pastes data over a row. For HR rosters that grow over time, the Table approach pays for itself in a month.

Let’s talk errors before we wrap up. DATEDIF can return #NUM! when the start date is later than the end date — usually because someone typed a year wrong or pasted the wrong column. #VALUE! means one of the arguments isn’t a date (often a text cell that looks like a date but isn’t). The bullet list below compares the two main approaches and helps you pick the right one for your situation.

Most practical workflows can use either. DATEDIF wins on conciseness and readability for whole-year age. YEARFRAC wins when partial years matter. The two are rarely interchangeable for advanced work, but for a basic roster either gets the job done in one cell. If your team is mixed-skill, default to DATEDIF — the function name describes what it does, and the syntax reads almost like a sentence.

A few more practical notes from the trenches. If you maintain a long-running spreadsheet, consider replacing TODAY() with a date cell at the top of the sheet (say, A1 = TODAY() entered as a static value or refreshed manually). TODAY() is volatile — it recalculates every time anything changes anywhere in the workbook, which slows large sheets to a crawl. A static date cell, referenced by every age formula, calculates once and stays put until you choose to refresh. On a 5,000-row HR roster, the difference between volatile and static can shave several seconds off every keystroke.

For shared workbooks, document your formulas. A one-line comment at the top of the column — “Age = DATEDIF(DOB, today, ‘Y’) — refresh A1 to update” — saves the next person from rebuilding the logic to verify it. Inherited spreadsheets without documentation are how subtle bugs survive for years. And if your sheet drives a downstream report — an HRIS sync, an audit deliverable, a dashboard — document the refresh cadence too, because nobody wants to defend a stale age column at a board meeting.

If you’re an analyst handing off work, consider one more polish step: convert the result column to a static value before sharing. Copy the column, Paste Special > Values, and the recipient sees a clean integer instead of a formula they might accidentally break. For internal working files, keep the formulas live; for external deliverables, freeze the numbers. The recipient gets what they actually need, you get a defensible snapshot of the data at the point of delivery.

One last test of the concepts before the FAQ. The practice quiz pulls from these formulas plus a handful of common Excel tasks you’ll see on certification exams and job interviews.

A quick recap so the key formulas stick. To get whole-year age: =DATEDIF(B2, TODAY(), "Y"). For decimals: =YEARFRAC(B2, TODAY(), 1). For years+months+days: chain three DATEDIF calls with the “Y”, “YM”, and “MD” units. For age on a past or future date: swap TODAY() for a fixed date. For dd/mm/yyyy text input: rebuild the date with DATE(RIGHT, MID, LEFT) before calling DATEDIF. That’s the entire chapter in five lines.

Excel does a lot of things by inference — it guesses your locale, your number formats, and the data type of every cell you touch. Age math is where those guesses become visible because a one-day misread compounds into a year-long error on the wrong side of a leap year. Pin down the input layer, use DATEDIF as your default, and the rest takes care of itself.

The FAQ below collects the questions that come up most often when teaching Excel to mixed-skill audiences. If you’re studying for an Excel certification exam, brushing up before an interview, or building a small HR sheet, treat the FAQ as a quick reference card. Same questions, same answers — just phrased the way they’ll appear on the test or in real conversations with your team.