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)

Search JabSto ::

Custom Search