DateDiff Problems with Elapsed Years

[mleary2001]

I am trying to calculate elapsed years using the DateDiff function on Mac OS X with Office X. It is just looking at the year part of the date, not the whole date, to do its calculations.

Here is my code:


Private Function health_insurance_function(constants_array As Variant, grant_start_date As Date, grant_year_count As Integer) As Currency

Dim insurance_start_date As Date
insurance_start_date = constants_array.Cells(22, 2)

Dim elapsed_insurance_years As Integer

elapsed_insurance_years = Abs(DateDiff("yyyy", grant_start_date, insurance_start_date))


This gives the following results:
6/1/05 --> 12/31/05 results in 0 years. Correct
6/1/05 --> 1/1/06 results in 1 year. Incorrect
6/1/05 --> 5/31/06 results in 1 year. Incorrect
6/1/05 --> 6/1/06 results in 1 year. Correct
6/1/05 --> 12/31/06 results in 1 year. Correct
6/1/05 --> 1/1/07 results in 2 years. Incorrect

Am I missing something?

Thanks in advance,
Mike

[matthewspatrick]

You might want to have a look at the YearsMonthsDays function I posted at:

http://www.vbaexpress.com/forum/showthread.php?t=7375

[mleary2001]

Thanks for the quick reply. I found this code on MSDN that passes all my tests:

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