Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

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

  1. #11
    Junior Member
    Join Date
    May 2013
    Posts
    8
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    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...
    Well, I really don't have to. I could just switch TODAY() - 1 to TODAY() + 1 to get an inverse because TODAY() - (TODAY() - 1) = (TODAY() + 1) - TODAY(). That's what I did, but I still got the #VALUE error.

    Quote Originally Posted by Rick Rothstein View Post
    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.
    Is there a VBA Doc place like the JavaDocs on Oracle's website where I could go look up that object? I would like to know more.

  2. #12
    Junior Member
    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0
    Hi Rick, thank you for this UDF - it is very useful. Unfortunately I am having some challenges using it to generate 'x years y months z days'-type output in Word. Because of the way the DateDiff() function in VBA works, I find that I have to do a manual work-around to count the right number of years/months/etc if the day of the month or month of the year in the second date is before the day/month in the first date. For example, DateDiff(FirstDate, SecondDate, "m"), where FirstDate = 20/02/2002 and SecondDate = 15/08/2002 returns 6, when it should only be 5.

    I've addressed this problem so that it seems to work in 99% of scenarios, but I'm still having the occasional problem where the number of years is miscounted, or where a day is dropped for some reason. The code I am using is below, and these are the results that it generates. I know this is a bit of a big one, but any assistance would be appreciated:

    FirstDate SecondDate Result Comments
    01/07/00 30/06/04 4 yrs 0 mths 0 days correct
    30/07/11 30/06/13 2 yrs 11 mths 1 days should be 1 yrs 11 mths 1 days
    15/02/02 20/08/02 0 yrs 6 mths 6 days correct
    15/02/02 20/08/03 1 yrs 6 mths 6 days correct
    20/02/02 15/08/02 0 yrs 5 mths 27 days correct
    20/02/02 15/08/03 1 yrs 5 mths 27 days correct
    20/08/01 15/02/02 0 yrs 5 mths 27 days correct
    20/08/01 15/02/04 2 yrs 5 mths 27 days correct
    01/07/00 30/06/01 1 yrs 0 mths 0 days correct
    08/07/11 08/06/13 1 yrs 11 mths 1 days correct
    30/10/96 30/08/05 8 yrs 10 mths 1 days
    [td]correct
    12/06/85 12/06/86 1 yrs 0 mths 1 days correct
    31/01/08 31/08/10 2 yrs 7 mths 0 days should be 2 yrs 7 mths 1 days
    31/01/08 31/08/09 1 yrs 7 mths 0 days should be 1 yrs 7 mths 1 days
    31/03/07 01/08/08 1 yrs 4 mths 2 days correct
    31/03/07 01/08/12 5 yrs 4 mths 2 days correct
    31/08/07 01/03/08 0 yrs 6 mths 2 days correct
    31/08/07 01/03/12 4 yrs 6 mths 2 days correct
    Last edited by Admin; 08-26-2013 at 09:32 AM. Reason: Removed the code.

  3. #13
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Thank you for alerting me to this. I thought I had checked all the "edge events" when I originally developed the code, but you are right... I obviously missed some. I will look into this tomorrow (my time... it is approaching midnight here now) and hopefully come up with a quick fix. Thanks again for bringing this to my attention.

  4. #14
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13

    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.

  5. #15
    Junior Member
    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0
    Hello again Rick,

    Sorry to do this on the public forum, but I'm unable to send you private messages. I know that you'll be busy with your 'normal' life, but was wondering how things were progressing with this bug...


    Regards,

    Christian

  6. #16
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    Alternative:

    Code:
    Function F_datediff_snb(x0 As Date, x1 As Date)
        n = DateDiff("m", x0, x1 + 1) + (Day(x1 + 1) < Day(x0))
        
        Y = n \ 12
        M = n Mod 12
        D = 1 + x1 - DateAdd("m", M, DateAdd("yyyy", Y, x0))
        
        F_datediff_snb = IIf(Y = 0, "", Y & " year" & IIf(Y = 1, " ", "s ")) & IIf(M = 0, "", M & " month" & IIf(M = 1, " ", "s ")) & IIf(D = 0, "", D & " day" & IIf(D = 1, " ", "s "))
    End Function
    Last edited by snb; 08-27-2013 at 11:43 PM.

  7. #17
    Junior Member
    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0
    Quote Originally Posted by snb View Post
    Alternative:

    Code:
    Function F_datediff_snb(x0 As Date, x1 As Date)
        n = DateDiff("m", x0, x1 + 1) + (Day(x1 + 1) < Day(x0))
        
        Y = n \ 12
        M = n Mod 12
        D = 1 + x1 - DateAdd("m", M, DateAdd("yyyy", Y, x0))
        
        F_datediff_snb = IIf(Y = 0, "", Y & " year" & IIf(Y = 1, " ", "s ")) & IIf(M = 0, "", M & " month" & IIf(M = 1, " ", "s ")) & IIf(D = 0, "", D & " day" & IIf(D = 1, " ", "s "))
    End Function
    That looks to have done the trick, nice and easy! Thanks snb!

  8. #18
    Member
    Join Date
    Dec 2012
    Posts
    43
    Rep Power
    0
    so, buttom line. is there any solution to the problem?

    has anyone tested which code is working now the one from Rick or the one from Snb?

  9. #19
    Member
    Join Date
    Dec 2012
    Posts
    43
    Rep Power
    0
    SnB

    your code also has problem

    1/31/2014 3/1/2014 between these two dates it shows 1 months and 2 days, while it is just 29 days.

  10. #20
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    @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.

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
  •