How to get a return value of the number of full years elapsed between two dates.
Q: William Butler writes: “I have been working on this problem all day, writing an expression in a query to show the difference between today’s date and one that just passed. It seems it keeps adding 1 year to my results if the time is not exactly on the year. I want only the exact number of years without the months. I have tried this expression, that Microsoft Help gave me but it does not do the job.
noyrs: DateDiff(“yyyy”,[effective_dt],Date())
I know it is something am missing from the statement to correct this. Can you help!!!!!!!!!!”
A: This is one of those “it’s not a bug, it’s a feature” situations. The Help topic for the DateDiff function says “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.” This is not what you (or most people, I think) would want, so to get a return value of the number of full years elapsed between two dates, you need a more complex function. The procedure below first checks whether the two dates being compared are exactly a year apart, using the DateAdd function with a negative number, and if they are, uses the unmodified DateDiff function to give the number of years; otherwise, it subtracts 1 from the DateDiff value to discard the partial year:
Public Function FullYears(dteOne As Date, dteTwo As Date) As Integer
On Error GoTo ErrorHandler
Dim dteTest As Date
Debug.Print “Standard DateDiff: ” & DateDiff(“yyyy”, dteOne, dteTwo)
‘Check whether dteOne is on the same day of the year as dteTwo — this
‘is the only case where DateDiff gives accurate results
dteTest = DateAdd(“yyyy”, -1, dteTwo)
Debug.Print “Test date: ” & dteTest
If dteTest = dteOne Then
FullYears = DateDiff(“yyyy”, dteOne, dteTwo)
Else
FullYears = DateDiff(“yyyy”, dteOne, dteTwo) – 1
End If
ErrorHandlerExit:
Exit Function
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Function