How to Calculate Age in Excel and Google Sheets With Formulas

In Excel and Google Sheets, the simplest method is DATEDIF. Put the date of birth in one cell and the later date in another, then use DATEDIF with Y for years, YM for months and MD for days. YEARFRAC gives a decimal age instead, so it is useful for estimates but not for a whole-years age.

Try the age calculator →

Worked example

Put the date of birth 14 February 1990 in cell A2 and the date 12 November 2026 in cell B2. The formula =DATEDIF(A2,B2,"Y") returns 36, because the birthday on 14 February 2026 has passed. The formula =DATEDIF(A2,B2,"YM") returns 8, and =DATEDIF(A2,B2,"MD") returns 29, because 14 February to 14 October is 8 months and 14 October to 12 November is 29 days. Together they give 36 years, 8 months and 29 days, which is 440 completed months and 13,420 days. The same three formulas work unchanged in Google Sheets.

How to calculate age in Excel with DATEDIF

DATEDIF is a function that finds the difference between two dates in years, months or days. It takes three parts: the start date, the end date and a unit code in quotes. The unit tells it which part of the age to return.

The unit Y returns completed years, YM returns the extra months after those years, and MD returns the extra days after those months. Used together, the three give the full age in years, months and days, with no rounding.

DATEDIF is an older function, so Excel does not always show it in the formula helper, but it still works when you type it. Google Sheets supports the same function with the same units, so the formulas in this guide work in both apps.

FormulaWhat it returns
=DATEDIF(A2,B2,"Y")Completed years, such as 36
=DATEDIF(A2,B2,"YM")Extra whole months, such as 8
=DATEDIF(A2,B2,"MD")Extra days, such as 29
=DATEDIF(A2,B2,"M")Total completed months, such as 440
=DATEDIF(A2,B2,"D")Total days, such as 13420

How do you set up the sheet?

Put the date of birth in cell A2 and the later date in cell B2, using real dates, not text. Type them as 14 February 1990 or in your usual date format, then check that the sheet treats them as dates by widening the column and looking at the alignment.

If a date is stored as text, DATEDIF will fail or give a wrong answer. You can spot text dates because they often sit on the left of the cell, while real dates sit on the right. Use the date format from the menu to fix them before you add formulas.

For an age today, use =TODAY() in B2 instead of a fixed date, so the age updates each day. For an age on a set date, type that date in B2. Keep the birth date in A2 unchanged, so the formulas always read from the same two cells.

How do you get years, months and days together?

Use three separate cells, one for each part. In C2, type =DATEDIF(A2,B2,"Y") for years. In D2, type =DATEDIF(A2,B2,"YM") for months. In E2, type =DATEDIF(A2,B2,"MD") for days.

Then join them into one readable result with a text formula, such as =C2&" years, "&D2&" months, "&E2&" days". That gives a clear line like 36 years, 8 months, 29 days, which is easy to copy into a note or a form check.

Keep the three number cells as well as the joined text, because the text result cannot be used in further maths. If you need to sort or filter by age, sort on the years cell, not on the joined text.

  1. C2: =DATEDIF(A2,B2,"Y") for completed years.
  2. D2: =DATEDIF(A2,B2,"YM") for extra months.
  3. E2: =DATEDIF(A2,B2,"MD") for extra days.
  4. Join them only for display, and keep the numbers for maths.

What about YEARFRAC and other methods?

YEARFRAC finds the fraction of a year between two dates, such as 36.75 years. The formula =YEARFRAC(A2,B2) gives a decimal, and =INT(YEARFRAC(A2,B2)) gives the completed years by dropping the fraction. It is handy, but the decimal depends on how YEARFRAC counts days.

YEARFRAC has a basis setting that changes how it counts the year length. The default basis counts actual days over an actual year, which is the closest to calendar age. Other bases assume 30-day months or a 360-day year, and those can shift the result by a few days.

A simple year subtraction, such as =YEAR(B2)-YEAR(A2), is not enough on its own, because it misses the birthday check. If you use it, add a check that subtracts one when the birthday has not happened yet, or use DATEDIF instead and avoid the extra step.

How do you avoid the common errors?

The #NUM! error usually means the start date is after the end date, or a unit is wrong. Check that the birth date is in A2 and the later date is in B2, and that the unit is one of Y, M, D, YM, YD or MD in quotes.

A #VALUE! error usually means a date is stored as text. Re-enter the date as a real date, or use DATEVALUE to convert it, then try the formula again. A wrong-looking age, such as 36 when you expect 35, usually means the birthday check or the date order is off.

Watch out for 29 February birthdays and month-end dates, because DATEDIF follows calendar rules for those. Test your sheet with a known case, such as your own last birthday, where the months and days should both be zero, before you trust it for other people.

Does Google Sheets work the same way?

Yes, Google Sheets supports DATEDIF with the same units, so you can copy the Excel formulas across unchanged. The set-up is the same: birth date in A2, later date in B2, and the three DATEDIF formulas for years, months and days.

Google Sheets also has TODAY(), YEARFRAC and the same date formats, so a sheet built in Excel will usually work in Sheets without changes. Check the date format after pasting, because the display format can differ even when the underlying date is the same.

If you share the sheet, lock the birth-date cells or add a note explaining which cells to change. That stops someone overwriting a formula by accident. The homepage age calculator on this site gives the same result without a sheet, if you only need one age.

Frequently asked questions

What is the best Excel formula for age?

DATEDIF is the best choice for an exact age. Use =DATEDIF(A2,B2,"Y") for completed years, with YM for extra months and MD for extra days. It applies the birthday check for you and counts calendar months correctly. YEARFRAC is better only when you want a decimal age rather than years and months.

Why does DATEDIF give a #NUM! error?

DATEDIF gives #NUM! when the start date is after the end date, or when the unit code is not valid. Check that the birth date is earlier than the later date and that the unit is Y, M, D, YM, YD or MD in quotes. Swapped dates are the most common cause, so check A2 and B2 first.

How do I calculate age as of today in Excel?

Put the birth date in A2 and =TODAY() in B2, then use =DATEDIF(A2,B2,"Y") for the years. TODAY() updates each day, so the age stays current without retyping a date. Add YM and MD formulas in the next cells if you also want months and days.

Does DATEDIF work in Google Sheets?

Yes. Google Sheets supports DATEDIF with the same start date, end date and unit codes as Excel. The formulas =DATEDIF(A2,B2,"Y"), =DATEDIF(A2,B2,"YM") and =DATEDIF(A2,B2,"MD") work unchanged. Check that both dates are real dates, not text, before you use them. If a formula shows an error, check the cell formats first, because a date stored as text is the usual cause in both apps.

How do I get age in months only in Excel?

Use =DATEDIF(A2,B2,"M") for total completed months, such as 440 for the worked example. That counts whole months since birth, not just the months after the last birthday. For the extra months after the years, use YM instead, which returns 8 in the same example.

How do I get age in days only in Excel?

Use =DATEDIF(A2,B2,"D") for total days, which returns 13,420 in the worked example. You can also use =B2-A2, which gives the same day count when both cells hold real dates. Format the result as a plain number, not as a date, or it will display wrongly.

What is the difference between DATEDIF and YEARFRAC?

DATEDIF returns whole years, months or days as separate numbers, which is exact for forms and records. YEARFRAC returns a decimal fraction of a year, such as 36.75, which is useful for estimates and finance but not for a whole-years age. Use DATEDIF for age, and YEARFRAC only when a decimal is what you need.

Why is my Excel age one year too high?

A result one year too high usually comes from a plain year subtraction, =YEAR(B2)-YEAR(A2), which misses the birthday check. DATEDIF with Y applies that check for you. If you keep the subtraction method, subtract one more year when the birthday in the later year falls after the later date.

Related calculators

Related guides

Sources: WhatsMyAge methodology · Methodology · Author: Shahzad · Last reviewed 2026-11-13.

This page is general information only. It is not medical, legal or financial advice.