The DATEDIF Function
This page has been replaced. Click here to go to the new page.
The DATEDIF function is a worksheet
function that computes the difference between two dates. This function is
available in all versions of Excel since version 5, but is documented only in the
help files for Excel 2000. It isn't documented in either Excel 97 or Excel
2002. DATEDIF has, for whatever reason, been
treated as one of the drunk cousins of the Function Family. Excel knows he
lives a happy and useful existence, and will acknowledge his existence when you
ask, but will never mention him in "polite" conversation. =DATEDIF(Date1,Date2,Interval) Where
Date1 must be less than (earlier) or equal to Date2. Otherwise, =DATEDIF will return a #NUM! error. If either Date1 or Date2 is not a date, =DATEDIF will return a #VALUE! error. Interval must be one of the following codes: 




When passing the interval code to the
DATEDIF
function, enclose it in quotes if you
are passing a literal value to the function: However, if your interval code is stored in a worksheet cell, it should not be enclosed in quotes in the cell. 


The table on the right displays the results of the various interval codes for two dates. Pay attention to the result returned by the ym interval code. The 5 indicates that there are 5 calendar months between January and June  the years are ignored. The same is true with the md interval. There are 14 calendar days between the two dates when the months and years are ignored.  
A frequent use of the
DATEDIF
is to compute someone's age based on the current date and their birthday.
The formula below will return someone's exact age based on their birthday in
cell A1. 

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" This will return a string like 33 years, 9 months, 18 days 

You can't use DATEDIF in VBA code. VBA provides a function called DateDiff (note, two f's), but DateDiff doesn't support the "ym", "md", and "yd" interval arguments that DATEDIF does. To compute age in VBA, you have to do the math on your own. Function Age(Date1 As Date, Date2 As Date) As String


=DATEDIF(Date1,Date2,"yd") will return 29, because there are 29 days between 1Feb1996 and 1Mar1996. If Date1 is changed to 1Feb1997 and Date2 remains 1Mar1997, the same function will return 28, the number of days between 1Feb1997 and 1Mar1997. Similarly, if Date1 is 1Feb1995 and Date2 is 1Mar1996, the result is 28 because both dates are given the year of the first date, which, in these last two examples, is not a leap year.


Other Date
And Time Related Procedures are described on the following pages. 

Holidays 

