How to Calculate Age Difference in Excel and Google Sheets

You are staring at two columns of birth dates in Excel, trying to figure out how to calculate the exact age difference between them. Simple subtraction gives you a messy decimal, and trying to divide by 365 leaves you with numbers that are slightly off every time. Frustration sets in when you realize Excel does not even autocomplete the function you actually need. The math is straightforward once you know the right formula. Here is the exact approach you need to get clean years, months, and days from a spreadsheet.

The Core Formula: DATEDIF

If you need an age difference calculator built right into Excel or Google Sheets, the function you want is DATEDIF.

Put the earlier date in cell A1 and the later date in cell B1. Then type these formulas exactly as written:

=DATEDIF(A1, B1, "y")    → returns whole years
=DATEDIF(A1, B1, "ym")   → returns leftover months
=DATEDIF(A1, B1, "md")   → returns leftover days

To string it all together into a readable output like "5 years, 8 months, 13 days", combine them in a single formula:

=DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months, " & DATEDIF(A1,B1,"md") & " days"

The best part? This works identically in Google Sheets. The function name and arguments translate perfectly between the two applications.

Understanding the DATEDIF Arguments

DATEDIF takes three arguments: the start date, the end date, and an interval code. The interval codes are not intuitive, so here is the full reference:

For an age difference breakdown, you need "y", "ym", and "md" together. The "m" and "d" codes give you totals — useful for "how many total months apart are they?" but not for the standard years-months-days breakdown.

Finding Total Units Instead of Breakdowns

Sometimes you want the gap measured in a single unit rather than the full breakdown. For total counts:

=DATEDIF(A1, B1, "d")    → total days between dates
=DATEDIF(A1, B1, "m")    → total complete months between dates
=(B1 - A1)               → plain subtraction also returns total days

Plain cell subtraction (=B1-A1) works for total days because Excel stores dates as sequential integers under the hood. The result is the raw number of days between the two dates — no months, no years.

The Known Microsoft Bug in "md"

The "md" argument — the one that calculates leftover days after subtracting complete years and months — has a long-documented bug in Excel. In specific leap-year and month-end scenarios, it can return a negative number or a clearly incorrect result.

The most common trigger conditions for the bug are:

• Month-end dates crossing leap years. Calculating from January 31 to March 1 in a leap year can produce wrong results for the "md" component.
• February edge cases. Any calculation involving dates at the end of February in a leap year versus a non-leap year is prone to the error.
• Long spans crossing multiple February 29ths. When the "md" component has to accumulate borrows across multiple month-end and leap-year combinations, the errors can compound.

The "y" and "ym" components are reliable. The "md" component is where you need to be careful.

For most everyday calculations — where the dates are not specifically month-end dates crossing a leap year — the bug does not appear. But if you are doing this for official records or large datasets, always spot-check a few results.

A More Robust Day-Count Alternative

If you need the "md" component to be perfectly reliable, here is a workaround formula that avoids the buggy code entirely:

=DAY(B1) - DAY(EDATE(A1, DATEDIF(A1,B1,"y")*12 + DATEDIF(A1,B1,"ym")))

This calculates the day component by finding the equivalent monthly anniversary date and subtracting day numbers directly, which sidesteps the leap-year edge cases. It is more complex but more reliable for precision work.

Formatting Dates Correctly for DATEDIF

DATEDIF only works when Excel recognizes your cells as actual date values, not text strings. If your dates were pasted or imported as text, you will get a #VALUE! error.

To verify a cell is a real date, click on it and check if the formula bar shows something like 3/15/1990 rather than "3/15/1990". If it shows quotation marks, it is text.

To convert text dates to real dates:

1. Select the column of text dates
2. Go to Data → Text to Columns
3. Choose Delimited → Next
4. Skip the delimiter step → Next
5. Under Column Data Format, select Date and pick the matching format (MDY, DMY, etc.)
6. Click Finish

Once the cells are real dates, DATEDIF will work correctly.

Working Across Multiple Rows

If you have a full spreadsheet with two columns of birth dates (Column A and Column B) and want to calculate all the age differences at once, the formula scales easily.

Put the combined formula in cell C1:

=DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months, " & DATEDIF(A1,B1,"md") & " days"

Then drag the formula down through all your rows. Excel and Google Sheets will automatically adjust the row references (A1→A2, B1→B2, etc.) as you go.

A Real-World Example of Where Year Subtraction Caused Problems

One memorable mistake: a company was calculating employee service anniversaries using simple year subtraction.

The formula was =YEAR(TODAY()) - YEAR(start_date). Looks reasonable. The problem was that the anniversary date had not actually occurred yet in the current year. The spreadsheet was reporting employees as having completed ten years of service when they were still several months short of the anniversary date.

It sounds minor. In practice, it affected recognition awards, internal reporting, and HR records — all based on an integer that looked correct but was off by almost a full year in some cases. The fix was switching to DATEDIF with proper date comparison rather than subtracting year numbers. The math was the same; the date awareness was not.

This kind of error is easy to introduce and surprisingly hard to spot because the output looks plausible. 2025 - 2015 = 10 reads as correct even when the person in question will not actually complete their tenth year for another eight months.

When to Use a Calculator Instead of Excel

Spreadsheets are powerful for processing dozens or hundreds of date pairs at once. But for one-off calculations, they are often overkill.

If you are trying to find the age difference between siblings, check a romantic partner's gap, or see how a celebrity couple's ages compare, a dedicated Age Difference Calculator is faster and eliminates any formula errors. It handles all the leap-year and month-length corrections automatically, and you get results without setting up a spreadsheet.

For calculating a single person's current age from their birthday, the Age Calculator is the right tool. For raw days between any two dates (without the breakdown), use the Date Difference Calculator.

Frequently Asked Questions

Why doesn't Excel suggest DATEDIF when I start typing?

DATEDIF is a hidden function in Excel — it is not in the function library and does not appear in autocomplete. Microsoft inherited it from Lotus 1-2-3 for compatibility and never officially documented it. You have to type it out exactly as written. It still works in all current versions of Excel and Google Sheets.

How do I calculate age difference in Google Sheets?

Exactly the same way as Excel. Type =DATEDIF(A1,B1,"y") for years, =DATEDIF(A1,B1,"ym") for remaining months, and =DATEDIF(A1,B1,"md") for remaining days. Google Sheets does not have the same "md" bug that some versions of Excel have, so it is generally more reliable for the day component.

My DATEDIF formula is returning #NUM! — what's wrong?

This error almost always means the start date is after the end date. DATEDIF requires the earlier date as the first argument. Either swap your cell references, or use MIN(A1,B1) as the start date and MAX(A1,B1) as the end date.

Can I calculate age differences from today's date automatically?

Yes. Replace the end date cell with TODAY() to always calculate against the current date. For example: =DATEDIF(A1,TODAY(),"y") gives the number of complete years from the date in A1 until today. This is useful for calculating someone's current age or how long ago a date occurred.

How do I get just the total number of days between two dates?

Either use =DATEDIF(A1,B1,"d") or simply =B1-A1. Both return the raw day count. Make sure cell formatting is set to Number rather than Date, or the result may display as a date value rather than an integer.