UDF (user defined function) replacement for Excel's DATEDIF function

[Rick Rothstein]

***************** ALERT *****************

In Message #12, reader 'ccallisen' has pointed out a problem with the code. I am working on a fix. If you see this alert message, then my advice is not to use the code. I'll post a fix as soon as possible

***************** ALERT *****************

Okay, the reason I posted my "http://www.excelfox.com/forum/f22/re...-function-321/" article was to setup this follow-up article which offers a UDF (user defined function) which I believe accurately duplicates the functionality that the now (possibly) unreliable DATEDIF function provided. (If you find date combinations for which it does not work correctly, please let me know so I can try to patch the code.)

Function xlDATEDIF(ByVal StartDate As Date, ByVal EndDate As Date, Interval As String) As Variant
  Dim NumOfYears As Long, NumOfMonths As Long, NumOfWeeks As Long, NumOfDays As Long
  Dim DaysDiff As Long, ydDaysDiff As Long, TSerial1 As Double, TSerial2 As Double
  If StartDate > EndDate Then
    Err.Raise 5
    Exit Function
  End If
  If InStr(1, "Y M D", Interval, vbTextCompare) Then
    Select Case UCase(Interval)
      Case "Y": xlDATEDIF = DateDiff("yyyy", StartDate, EndDate)
      Case "M": xlDATEDIF = DateDiff("m", StartDate, EndDate)
      Case "D": xlDATEDIF = EndDate - StartDate
    End Select
  Else
    NumOfYears = DateDiff("yyyy", StartDate, EndDate)
    DaysDiff = EndDate - StartDate
    TSerial1 = TimeSerial(Hour(StartDate), Minute(StartDate), Second(StartDate))
    TSerial2 = TimeSerial(Hour(EndDate), Minute(EndDate), Second(EndDate))
    If 24 * (TSerial2 - TSerial1) < 0 Then EndDate = DateAdd("d", -1, EndDate)
    StartDate = DateSerial(Year(EndDate), Month(StartDate), Day(StartDate))
    If StartDate > EndDate Then
      StartDate = DateAdd("yyyy", -1, StartDate)
      NumOfYears = NumOfYears - 1
    End If
    ydDaysDiff = EndDate - StartDate
    NumOfMonths = DateDiff("m", StartDate, EndDate)
    StartDate = DateSerial(Year(EndDate), Month(EndDate), Day(StartDate))
    If StartDate > EndDate Then
      StartDate = DateAdd("m", -1, StartDate)
      NumOfMonths = NumOfMonths - 1
    End If
    NumOfDays = Abs(DateDiff("d", StartDate, EndDate))
    Select Case UCase(Interval)
      Case "YM": xlDATEDIF = NumOfMonths
      Case "YD": xlDATEDIF = ydDaysDiff
      Case "MD": xlDATEDIF = NumOfDays
      Case Else
    End Select
  End If
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use xlDATEDIF just like it was a built-in Excel function. For example,

=xlDATEDIF(A1,B1)

[in.vaibhav]

Hi

Where exactly built in datedif function doesnt work, can you please share some example.

Rgds

[Rick Rothstein]

Where exactly built in datedif function doesnt work, can you please share some example.

I think you may have missed the link at the beginning of my article... it contains all my arguments why against using DATEDIF and includes an example. Here is the link again...

Recommendation: Do not use the undocumented DATEDIF function

[snb]

Rick,

Wouldn't this be an alternative ?
Interval can be "y","m","d", or "yy","mm","dd"

Function xlDATEDIF(ByVal StartDate As Date, ByVal EndDate As Date, Interval As String) As Variant
  If StartDate > EndDate Then
    Err.Raise 5
    Exit Function
  End If
   
   If Len(Interval) = 1 Then
        xlDATEDIF = Choose(InStr("YMD", Interval), Year(EndDate) - Year(StartDate), DateDiff("m", StartDate, EndDate), EndDate - StartDate)
   Else
        NumOfYears = Year(EndDate) - Year(StartDate) - IIf(100 * Month(StartDate) + Day(StartDate) > 100 * Month(EndDate) + Day(EndDate), 1, 0)
        NumOfMonths = DateDiff("m", StartDate, EndDate) - IIf(Day(StartDate) > Day(EndDate), 1, 0)
        DaysDiff = EndDate - StartDate - IIf(Format(StartDate, ",0000") * 1 > Format(EndDate, ",0000") * 1, 1, 0)
        
        xlDATEDIF = Choose(InStr("YMD", Right(Interval, 1)), NumOfYears, NumOfMonths, daysdiff)
  End If
End Function

[Rick Rothstein]

Function xlDATEDIF(ByVal StartDate As Date, ByVal EndDate As Date, Interval As String) As Variant
  If StartDate > EndDate Then
    Err.Raise 5
    Exit Function
  End If
   
   If Len(Interval) = 1 Then
        xlDATEDIF = Choose(InStr("YMD", Interval), Year(EndDate) - Year(StartDate), DateDiff("m", StartDate, EndDate), EndDate - StartDate)
   Else
        NumOfYears = Year(EndDate) - Year(StartDate) - IIf(100 * Month(StartDate) + Day(StartDate) > 100 * Month(EndDate) + Day(EndDate), 1, 0)
        NumOfMonths = DateDiff("m", StartDate, EndDate) - IIf(Day(StartDate) > Day(EndDate), 1, 0)
        DaysDiff = EndDate - StartDate - IIf(Format(StartDate, ",0000") * 1 > Format(EndDate, ",0000") * 1, 1, 0)
        
        xlDATEDIF = Choose(InStr("YMD", Right(Interval, 1)), NumOfYears, NumOfMonths, dasdiff)
  End If
End Function

I haven't gone through you code yet, but is the text I highlighted in red a typo for DaysDiff?

[snb]

Yes, it is/was
So to improve readability:

Function xlDATEDIF(ByVal StartDate As Date, ByVal EndDate As Date, Interval As String) As Long
  If StartDate < EndDate Then
    sp = Split("Y|M|D|YY|MM|DD", "|")
    sn = Array(0, 0, 0, 0, 0, 0)

    sn(0) = Year(EndDate) - Year(StartDate)
    sn(1) = DateDiff("m", StartDate, EndDate)
    sn(2) = EndDate - StartDate
    sn(3) = Year(EndDate) - Year(StartDate) - IIf(100 * Month(StartDate) + Day(StartDate) > 100 * Month(EndDate) + Day(EndDate), 1, 0)
    sn(4) = DateDiff("m", StartDate, EndDate) - IIf(Day(StartDate) > Day(EndDate), 1, 0)
    sn(5) = EndDate - StartDate - IIf(Format(StartDate, ",0000") * 1 > Format(EndDate, ",0000") * 1, 1, 0)
    
    xlDATEDIF = sn(Application.Match(Interval, sp, 0) - 1)
  Else
    Err.Raise 5
  End If
End Function

[ProspectiveCounselor]

Hello,

I have a couple of problems with the code. For reference, I'm using Windows 7 and Excel XP.

I hate to bring up dead threads, but I was looking to do a DATEDIF function for Excel so that I could subtract dates. I tried all three sets of code provided in the code boxes, but I keep getting a #VALUE! error. I'm pretty new to VBA, though I have a few years of experience in PHP, C++, and Java (and a few minutes' experience with Perl), so I can't go in and debug because I don't understand all the terms. Can one of you tell me what I'm doing wrong? Here are my cells, so you guys can replicate the problem.

A1 = today()
B1 = today() + b2
B2 = 60 //I also used -60 to see if I had the dates backwards.
C1 = xldatedif(A1,B1)

[Rick Rothstein]

Hello,

I have a couple of problems with the code. For reference, I'm using Windows 7 and Excel XP.

I hate to bring up dead threads, but I was looking to do a DATEDIF function for Excel so that I could subtract dates. I tried all three sets of code provided in the code boxes, but I keep getting a #VALUE! error. I'm pretty new to VBA, though I have a few years of experience in PHP, C++, and Java (and a few minutes' experience with Perl), so I can't go in and debug because I don't understand all the terms. Can one of you tell me what I'm doing wrong? Here are my cells, so you guys can replicate the problem.

A1 = today()
B1 = today() + b2
B2 = 60 //I also used -60 to see if I had the dates backwards.
C1 = xldatedif(A1,B1)

You are missing the required 3rd argument... the code letter(s) for the interval whose date difference you want calculated. For example, to get the number of days between the dates in A1 and B1, you formula in C1 would need to look like this (what I highlighted in red is what you forgot to include)...

C1 = xldatedif(A1,B1,"d")

I am presuming since you are looking for the replacement to Excel's DATEDIF function, that you already know all the interval code letters.

[ProspectiveCounselor]

OK, that fixed it, but only the code you originally provided works properly. The second one gives me a value of 0 when I subtract today() from today() - 1, and the last one still gives me a #VAULE! error. I'd like to be able to use the last one because of its compactness, but any one that I can use is acceptable.

I have a question about the code. What does Err.Raise 5 do? Also, is "Select Case" like a switch statement?

[Rick Rothstein]

OK, that fixed it, but only the code you originally provided works properly. The second one gives me a value of 0 when I subtract today() from today() - 1, and the last one still gives me a #VAULE! error. I'd like to be able to use the last one because of its compactness, but any one that I can use is acceptable.

I think your problem is you did not inverse the arguments when you used TODAY()-1... if you look at the header for the function, namely...

Function xlDATEDIF(ByVal StartDate As Date, ByVal EndDate As Date, Interval As String) As Variant

you will see that the first argument is the StartDate (that is, the earlier date) and the second argument is the EndDate (the later date), since TODAY()-1 is earlier than TODAY(), it should be the first argument in the function... I have a feeling you did not do that. By the way, the only code I can speak to is the main, "longer" code in the first message... the other code was written by a contributor named 'snb'. I would point out that my code is longer because it provides for all of the original DATEDIF function that it is modeled after... in looking at the code provided by 'snb', I note he does not provide for the YM, YD or MD interval options. You should keep that in mind when deciding on using the "shorter" code over the "longer" code.

I have a question about the code. What does Err.Raise 5 do? Also, is "Select Case" like a switch statement?

As the name implies, it raises an error, to be exact, it raises the "Invalid procedure call or argument" error (it is how the program communicates to the outside world that something is wrong with one or more of the arguments to the function when, in fact, something is actually wrong with the argument that has been passed into the function.