Microsoft Office Tutorials and References

In Depth Information

**Tip 38: Calculating a Person’s Age**

About the DATEDIF function

The DATEDIF function, which isn’t documented in the Excel Help system, is one of the little Excel

mysteries. Although the Excel 2000 Help system has an entry for DATEDIF, the function is not

documented in earlier or later versions.

The old Lotus 1-2-3 spreadsheet program introduced DATEDIF, and Excel likely included the

function for compatibility purposes.

DATEDIF calculates the difference between two dates and expresses the result in terms of months,

days, or years. The syntax for the DATEDIF function is

=DATEDIF(Date1,Date2,Interval)

Date1
and
Date2
are standard dates (or a reference to a cell that contains a date).
Date1
must be

earlier (or equal to)
Date2
. The third argument, Interval, is a text string that specifies the unit of time

that will be returned.

Valid interval codes are described in this list:

The number of complete months between
Date1
and
Date2
.

●

The number of days between
Date1
and
Date2
.

●

The number of complete years between
Date1
and
Date2
.

●

ym:
The number of months between
Date1
and
Date2
. This interval excludes years, so it works

as though the two dates are in the same year.

●

yd:
The number of days between
Date1
and
Date2
. This interval excludes years, so it works as

though
Date1
and
Date2
are in the same year.

●

md:
The number of days between
Date1
and
Date2
. This interval excludes both month and

year, so it works as though
Date1
and
Date2
are in the same month and the same year.

●

If you’re a stickler for accuracy, here’s another version:

=DATEDIF(B1,B2,”y”) & “ years, “&DATEDIF(B1,B2,”ym”) &

“ months, “&DATEDIF(B1,B2,”md”) & “ days”

This function returns a text string, like this:

33 years, 8 months, 17 days