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

[Rick Rothstein]

***************** 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/recommendation-do-not-use-undocumented-datedif-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.)


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)

[in.vaibhav]

Hi

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

Rgds

[Rick Rothstein]

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 (http://www.excelfox.com/forum/f22/recommendation-do-not-use-undocumented-datedif-function-321/)

[snb]

Rick,

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


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

[Rick Rothstein]

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?

[snb]

Yes, it is/was
So to improve readability:


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

[ProspectiveCounselor]

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)

[Rick Rothstein]

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.

[ProspectiveCounselor]

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?

[Rick Rothstein]

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.


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.

[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
[td]8 yrs 10 mths 1 days
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.

[jamilm]

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

[bassem.dar]

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,




***************** 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/recommendation-do-not-use-undocumented-datedif-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.)


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)