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

[ProspectiveCounselor]

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.

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.

[ccallisen]

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

[Rick Rothstein]

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.

[Rick Rothstein]

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.

[ccallisen]

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

[snb]

Alternative:

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

[ccallisen]

Alternative:

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!

[jamilm]

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?

[jamilm]

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.

[snb]

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