If you have ever needed to calculate the age between two dates, then the function below is for you. Often people mistakenly believe that the age calculation can simply be accomplished using the DateDiff() function, but this is not the case and a slightly more complex function is required to do the job.

To merely calculate the age of an individual in years, you can simply use the DateDiff().

=DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") > Format(Date(), "mmdd"), 1, 0)

However, if you would like a little more detail (Years, Months, Days) you can use a function such as the one presented below.

Function fAge(dteStart As Variant, dteEnd As Variant) As Variant
'*******************************************
'Purpose:   Accurately return the difference
'           between two dates, expressed as
'           years.months.days
'Coded by:  raskew (from MS Access forum)
'Inputs:    From debug (immediate) window
'           1) ? fAge(#12/1/1950#, #8/31/2006#) 'Calculate btw 2 specific dates
'           2) ? fAge(#12/30/2005#, Date()) ' Calculate as of today's date
'*******************************************
Dim intHold   As Integer
Dim dayhold   As Integer

   'correctly return number of whole months difference
   'the (Day(dteEnd) < Day(dteStart)) is a Boolean statement
   'that returns -1 if true, 0 if false
   intHold = DateDiff("m", dteStart, dteEnd) + (Day(dteEnd) < Day(dteStart))

   'correctly return number of days difference
   If Day(dteEnd) < Day(dteStart) Then
      dayhold = DateDiff("d", dteStart, DateSerial(Year(dteStart), Month(dteStart) + 1, 0)) + Day(dteEnd)
   Else
      dayhold = Day(dteEnd) - Day(dteStart)
   End If

   fAge = LTrim(Str(intHold \ 12)) & " years " & LTrim(Str(intHold Mod 12)) & " months " & LTrim(Str(dayhold)) & " days"

End Function