Ages and durations with DATEDIF

How DATEDIF works

• What does DATEDIF do?

  It counts the whole years, months or days between two dates. The start date goes first:

  =DATEDIF(B2,$F$1,"Y")

• Which units are there?

  • "Y": whole years, for ages and years of service.
  • "M": whole months.
  • "D": days, the same as subtracting.
  • "YM": months left over after the whole years, so you can write "35 years, 4 months".
  • "MD" and "YD": days ignoring months, or ignoring years. "MD" can give odd results at month ends, so avoid it.

• Why not divide by 365?

  =(F1-B2)/365 drifts because of leap years and gives fractions, so someone the day before their birthday can come out a year older. YEAR(F1)-YEAR(B2) is wrong for everyone whose birthday hasn't come yet this year. DATEDIF counts the way people do.

• Why isn't it in Excel's function list?

  DATEDIF came from Lotus 1-2-3 and Microsoft never documented it fully, so Excel doesn't suggest it as you type. It works in every version, and in Google Sheets.

• What about today's age?

  Use TODAY() as the end date: =DATEDIF(B2,TODAY(),"Y"). Here F1 holds a fixed date so the answers don't change from day to day.