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)