Microsoft Office Tutorials and References
In Depth Information
Tip 38: Calculating a Person’s Age
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
Search JabSto ::

Custom Search