CODEWITHSUNDEEP
ms-accessvba
Rick
Rick

Reputation: 2308

VBA: DateDiff Year off

I have the following code in VBA to return the year between two dates: DateDiff("yyyy", "10/10/1930","06/07/2008 8:30:00 AM")
It returns 78, but it should really be 77.
What is going on here?

Upvotes: 2

Views: 3375

Answers (4)

James
James

Reputation: 22246

Working as designed: see Remarks (larger intervals) under http://msdn.microsoft.com/en-us/library/b5xbyt6f%28v=vs.80%29.aspx WHICH STATES:

If Interval is set to DateInterval.Year, the return value is calculated purely from the year parts of Date1 and Date2.

When you do datediff by year the operation performed is 2008 - 1930

Upvotes: 0

mellamokb
mellamokb

Reputation: 56779

Edit: According to this (VB.Net) As pointed out by @Justin, this is for VB.Net, not Visual Basic, but the implementation is most likely identical for backwards compatibility. I've referenced the relevant VBScript documentation below for completeness.

Larger Intervals. If Interval is set to DateInterval.Year, the return value is calculated purely from the year parts of Date1 and Date2. Similarly, the return value for DateInterval.Month is calculated purely from the year and month parts of the arguments, and for DateInterval.Quarter from the quarters containing the two dates.

For example, when comparing December 31 to January 1 of the following year, DateDiff returns 1 for DateInterval.Year, DateInterval.Quarter, or DateInterval.Month, even though at most only one day has elapsed.

Also see this (VBScript):

When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed.

So it's likely implemented like this, which gives 78:

Year(SecondDate) - Year(FirstDate)

See @Ken's solution for an implementation that should work as intended.

Upvotes: 4

Ken Gregory
Ken Gregory

Reputation: 7400

VBA's DateDiff function was not designed to track elapsed time. That statement is simply evaluating the year.

See this msdn article, which offers a function to calculate the years elapsed: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/workingwithelapsedtime.asp

Function elapsed_years_function(first_date As Date, Optional second_date As Date = 0) As Integer
' This procedure is from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/workingwithelapsedtime.asp
Dim elapsed_years As Integer
If second_date = 0 Then
' Did the caller pass in a date? If not, use
' the current date.
second_date = Date
End If
elapsed_years = DateDiff("yyyy", first_date, second_date)
If second_date < DateSerial(Year(second_date), Month(first_date), Day(first_date)) Then
elapsed_years = elapsed_years - 1
End If
elapsed_years_function = elapsed_years
End Function

Upvotes: 5

Shaded
Shaded

Reputation: 17876

If you did DateDiff("yyyy", "12/31/2010", "1/1/2011") it would return 1, even though there is only a day difference.

Upvotes: 2

Related Questions