How to calculate age in Excel and Google Sheets

Spreadsheets store dates as numbers (days counted from a fixed starting date), which makes age calculations quick once you know the right functions. Every formula here works in both Microsoft Excel and Google Sheets. In each one, A2 holds the date of birth.

Age in whole years

The workhorse is DATEDIF, which counts complete units between two dates:

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

This returns the number of complete years, so it only goes up on the birthday itself, exactly how age is normally counted. TODAY() updates every time the sheet recalculates.

In Excel, DATEDIF doesn’t appear in the suggestions as you type, because Microsoft keeps it mainly for compatibility with older spreadsheets. Type it in full and it works. In Google Sheets it’s a documented, fully supported function.

Years, months and days

To show an age like “31 years, 2 months, 15 days”, combine three DATEDIF calls:

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

"YM" gives the months left over after the complete years, and "MD" the days left over after the complete months.

There’s a catch: Microsoft warns that the "MD" unit “may result in a negative number, a zero, or an inaccurate result” and recommends against using it. A safer way to get the days is to step forward by the complete months with EDATE and subtract:

=TODAY() - EDATE(A2, DATEDIF(A2, TODAY(), "M"))

DATEDIF(…,"M") counts the complete months since birth, EDATE adds that many months to the date of birth, and the difference to today is the remaining days. Format the cell as a number. Near the end of a month this can still differ by a day from our age calculator because of how spreadsheets decide when a month is complete; our guide to why age calculators disagree explains why.

Age as a decimal

For a fractional age, such as 31.21, use YEARFRAC with basis 1 (actual days in actual years):

=YEARFRAC(A2, TODAY(), 1)

Wrap it in ROUND(…, 2) to show two decimal places. Avoid INT(YEARFRAC(…)) for whole years: near birthdays, rounding inside YEARFRAC can tip the result the wrong way. DATEDIF with "Y" is the reliable choice for whole years.

Age in days, weeks and months

Because dates are day numbers, age in days is a plain subtraction:

=TODAY() - A2

If the result looks like a date, change the cell format to Number. From there:

• Weeks: =INT((TODAY()-A2)/7)
• Complete months: =DATEDIF(A2, TODAY(), "M")
• Hours: =(TODAY()-A2)*24

Our age in days calculator shows all of these at once, with upcoming day-count milestones.

Age on a specific date

Every formula above uses TODAY(). To calculate age on another date, such as a cut-off date for school or an event, replace it with a cell that holds the date, or with a DATE(year, month, day) function:

=DATEDIF(A2, B2, "Y")
=DATEDIF(A2, DATE(2026, 12, 31), "Y")

Next birthday and days to go

To find the next birthday, add the current age plus one, in months, to the date of birth:

=EDATE(A2, 12 * (DATEDIF(A2, TODAY(), "Y") + 1))

Subtract TODAY() from that to get the number of days until the birthday. EDATE moves a 29 February birthday to 28 February in common years, matching our calculators’ default.

Pitfalls to avoid

• Dates stored as text. If a formula returns #VALUE!, the date may be text, not a real date. Convert it with DATEVALUE, or re-enter it in a format your spreadsheet’s locale understands.
• Day/month mix-ups. 04/08/1995 is 4 August in some locales and April 8 in others. Check a few dates after importing data, or use unambiguous ISO dates like 1995-08-14.
• Dates before 1900. Excel’s date system starts in 1900, so it can’t calculate with earlier dates directly, and it treats 1900 as a leap year for compatibility with older software, which affects dates before 1 March 1900. Google Sheets handles earlier dates.
• The “MD” unit. As above, avoid it and use the EDATE method for days.

For a one-off answer, or to check a spreadsheet, our age calculator and date difference calculator give the same results in a couple of taps.