DATEDIF Function

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.

You can use DATEDIF as you would any normal worksheet function, because it is a normal worksheet function. Until Excel2000, it was never documented in the help files, but it has been around since at least Excel5. There has been, from time to time, an article in the Knowledge Base describing DATEDIF, but as often as not, the article is not available. Why? Who knows? One of the deep dark secrets known only the Softies.

The syntax for DATEDIF is as follows:

=DATEDIF(Date1,Date2,Interval)

Where

Date1 is the first date, in standard Excel serial-date format.
Date2 is the second date, in standard Excel serial-date format.
Interval indicates the unit of time that =DATEDIF is to return is result.

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:

Code	Meaning	Description
"m"	Months	The number of complete months between Date1 and Date2.
"d	Days	The number of days between Date1 and Date2.
"y"	Years	The number of complete years between Date1 and Date2.
"ym"	Months Excluding Year	The number of months between Date1 and Date2, as if Date1 and Date2 were in the same year.
"yd"	Days Excluding Years	The number of days between Date1 and Date2, as if Date1 and Date2 were in the same year.
"md"	Days Excluding Months And Years	The number of days between Date1 and Date2, as if Date1 and Date2 were in the same month and the same year.

When passing the interval code to the DATEDIF function, enclose it in quotes if you are passing a literal value to the function:

=DATEDIF(A1,NOW(),"d")

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.

Calculating A Person's Age

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
    Dim Y As Integer
    Dim M As Integer
    Dim D As Integer
    Dim Temp1 As Date
    Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
    Y = Year(Date2) - Year(Date1) + (Temp1 > Date2)
    M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2))
    D = Day(Date2) - Day(Date1)
    If D < 0 Then
        M = M - 1
        D = Day(DateSerial(Year(date2), Month(date2), 0)) + D
    End If
    Age = Y & " years " & M & " months " & D & " days"
End Function

DATEDIF And Leap Years

When you are using the yd interval, Excel will use the year of the first date, Date1, to determine whether the to include 29-February in the calculation. In other words, it uses the year of Date1 as the year for both dates. For example, if Date1 is 1-Feb-1996, and Date2 is 1-Mar-1997,

=DATEDIF(Date1,Date2,"yd")

will return 29, because there are 29 days between 1-Feb-1996 and 1-Mar-1996. If Date1 is changed to 1-Feb-1997 and Date2 remains 1-Mar-1997, the same function will return 28, the number of days between 1-Feb-1997 and 1-Mar-1997. Similarly, if Date1 is 1-Feb-1995 and Date2 is 1-Mar-1996, 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.

Adding Months And Years

Date And Time Arithmetic

Distributing Dates Over Intervals

Holidays

Julian Dates

Latitude And Longitude

Overtime Hours And Timesheets

Time Zones In VBA

VBA Procedures For Dates And Times

Week Numbers

Worksheet Functions For Dates And Times

See the Dates And Times Topic Index For Information