UDF (user defined function) replacement for Excel's DATEDIF function
***************** 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.)
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)
ALERT -- Main article code has a bug!
I am posting this message so that all those subscribed to this thread will be alerted to a problem with the main article's code. In Message #12, reader 'ccallisen' has pointed out certain circumstances where the code in the main article will produce incorrect result. I have narrowed the problem down to the fact that VB's DateDiff function (whose functionality I relied on) works differently than Excel's DATEDIF function (who would have thought). Anyway, I am working on a fix and will post a message to this thread once I have corrected the problem. Until then... you are warned.
