Why doesn't Excel suggest DATEDIF?

It is a function inherited from Lotus 1-2-3, kept for compatibility but not documented in the function wizard. It works anyway.

The DATEDIF function

The gap between two dates, in complete years, months or days: an age, a length of service, a contract duration. A hidden function: Excel does not suggest it as you type.

Syntax

=DATEDIF(start_date, end_date, unit)

In a French Excel: =DATEDIF(date_début; date_fin; unité).

Arguments

start_date and end_date: in that order.
unit: "y" years, "m" months, "d" days, "ym" months beyond the years, "md" days beyond the months. The codes are the same in every Excel language.

An example

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

A length of service in the form "7 years and 4 months".

Pitfalls

Dates reversed: #NUM!.
Dates stored as text: #VALUE!; Fix dates first.
The "md" unit sometimes gives wrong results at month ends; Microsoft advises against it.

Going further

NETWORKDAYS, for working days.
Find the right formula: "calculate the gap between two dates".

Frequently asked questions

Why doesn't Excel suggest DATEDIF?: It is a function inherited from Lotus 1-2-3, kept for compatibility but not documented in the function wizard. It works anyway.