Excel DATEDIF: years, months or days between two dates

DATEDIF counts the full years, months or days between two dates. Excel hides it from the suggestions list, but it works in every version.

The formula

=DATEDIF(start_date, end_date, unit)

The start date must be the earlier one.

Steps: someone’s age

1. Birth date in B1.
2. Click B2. Type =DATEDIF(B1, TODAY(), "y"). Enter.

Result: their age in full years.

The units

“3 years, 4 months” in one cell

=DATEDIF(B1,B2,"y") & " years, " & DATEDIF(B1,B2,"ym") & " months"

Check it worked

Pick two dates exactly one year apart. "y" must give 1 and "ym" must give 0.

Common mistakes

#NUM! — the start date is after the end date. Swap them.

The date is text. A date typed as “12/03/2019” in a Text cell is not a date. Set the format to Date and re-enter it.

Only need days? Skip DATEDIF: subtract the dates.