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

[jamilm]

@jamlin

I don't think so.
If I say to you on the 31th of january: "see you next month" I expect to see you before the first of march.

thanks. i am not sure how this works but i found a suloltion which is this formula =INT((B2-A2)/365.25) & " years " & INT(MOD((B2-A2)/365.25,1)*12) & " months " & INT(MOD((B2-A2)/30.4375,1)*30.4375) & " days"

it worked for me perfectly.

[bassem.dar]

Hi, I have used your code, thank you so much for that. but i have serious problem as i am using for about 2000 transaction, randomly, most calculated correct and some not correct in calculation, the problem is that I can't go through the 2000. here under an example.

female urban 19/04/2005 18/03/2014 9 Years 10 Month 27 Days , it should be 8 years and 11 month

this is calculated correctly, female urban 10/12/2005 18/03/2014 9 Years 3 Month 8 Days

please support,

***************** 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)