Results 1 to 10 of 22

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13

    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)
    Last edited by Rick Rothstein; 08-03-2013 at 01:12 AM.

Similar Threads

  1. Recommendation: Do not use the undocumented DATEDIF function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 4
    Last Post: 05-16-2015, 10:06 PM
  2. How To Use Frequency Function In Excel
    By Transformer in forum Familiar with Commands and Formulas
    Replies: 1
    Last Post: 04-09-2013, 11:49 AM
  3. Creating drop-down function in excel
    By Jorrg1 in forum Excel Help
    Replies: 4
    Last Post: 01-09-2013, 01:45 PM
  4. Nested Search Function Excel Formula
    By trankim in forum Excel Help
    Replies: 6
    Last Post: 10-29-2012, 10:29 PM
  5. Excel Nested IF Function With 3 Conditions
    By patsir in forum Excel Help
    Replies: 3
    Last Post: 08-25-2012, 07:15 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •