Page 1 of 3 123 LastLast
Results 1 to 10 of 22

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

Hybrid 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.

  2. #2
    Junior Member
    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    0
    Hi

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

    Rgds
    Last edited by in.vaibhav; 10-16-2012 at 07:25 PM.

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by in.vaibhav View Post
    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

  4. #4
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Rick,

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

    Code:
    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
    Last edited by snb; 10-18-2012 at 02:46 AM.

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    Code:
    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?
    Last edited by Rick Rothstein; 10-18-2012 at 12:42 AM.

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Yes, it is/was
    So to improve readability:

    Code:
    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
    Last edited by snb; 10-19-2012 at 02:08 PM.

  7. #7
    Junior Member
    Join Date
    May 2013
    Posts
    8
    Rep Power
    0
    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)

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by ProspectiveCounselor View Post
    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.
    Last edited by Rick Rothstein; 05-05-2013 at 08:11 PM.

  9. #9
    Junior Member
    Join Date
    May 2013
    Posts
    8
    Rep Power
    0
    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?

  10. #10
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by ProspectiveCounselor View Post
    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.

    Quote Originally Posted by ProspectiveCounselor View Post
    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.

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
  •