Microsoft Office 2000/Visual Basic Programmer's Guide

Calculating Elapsed Time

You can use the DateAdd and DateDiff functions to calculate the time that has elapsed between two dates, and then, with a little additional work, present that time in the desired format. For example, the following procedure calculates a person's age in years, taking into account whether his or her birthday has already occurred in the current year.

Using the DateDiff function to determine the number of years between today and a birthdate doesn't always give a valid result because the DateDiff function rounds to the next year. If a person's birthday hasn't yet occurred, using the DateDiff function will make the person one year older than he or she actually is.

To remedy this situation, the procedure checks to see whether the birthday has already occurred this year, and if it hasn't, it subtracts 1 to return the correct age.

Function CalcAge(dteBirthdate As Date) As Long

   Dim lngAge As Long
   
   ' Make sure passed-in value is a date.
   If Not IsDate(dteBirthdate) Then
      dteBirthdate = Date
   End If
   
   ' Make sure birthdate is not in the future.
   ' If it is, use today's date.
   If dteBirthdate > Date Then
      dteBirthdate = Date
   End If
   
   ' Calculate the difference in years between today and birthdate.
   lngAge = DateDiff("yyyy", dteBirthdate, Date)
   ' If birthdate has not occurred this year, subtract 1 from age.
   If DateSerial(Year(Date), Month(dteBirthdate), Day(dteBirthdate)) > Date Then
      lngAge = lngAge - 1
   End If
   CalcAge = lngAge
End Function

This procedure is available in the modDateTime module in VBA.mdb in the ODETools\V9\Samples\OPG\Samples\CH07 subfolder on the Office 2000 Developer CD-ROM.