Microsoft Office Tutorials and References

In Depth Information

**Using the DATEDIF Function**

Using the DATEDIF Function

In Tip 97, I present a formula that uses the DATEDIF function, which calculates the difference

between two dates and expresses the result in terms of months, days, or years.

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

mysteries. Although the Excel 2000 Help system has an entry for DATEDIF, it’s 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. But, for some reason, Microsoft doesn’t want to acknowledge

its existence. Lawyers are probably somehow involved in this mystery.

In any case, this tip describes the DATEDIF function and tells you what Microsoft isn’t willing to

tell you. 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 than (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:

h
The number of complete months between Date1 and Date2.
m:

h
The number of days between Date1 and Date2.
d:

h
The number of complete years between Date1 and Date2.
y:

h
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.

h
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.

h
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.

Figure 100-1 shows a few examples of using the DATEDIF function with each of its possible

interval arguments. The formula in D2, which was copied to the cells below, is:

=DATEDIF(A2,B2,C2)