Skip to content

Calculating Years

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

About this author