Page 3 of 3 FirstFirst 123
Results 21 to 22 of 22

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

  1. #21
    Member
    Join Date
    Dec 2012
    Posts
    43
    Rep Power
    0
    Quote Originally Posted by snb View Post
    @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.

  2. #22
    Junior Member
    Join Date
    Mar 2015
    Posts
    1
    Rep Power
    0

    Hi, please suuport.

    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,



    Quote Originally Posted by Rick Rothstein View Post
    ***************** 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)

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
  •