Calculating Elapsed Time

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

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 does not always give a valid result because the DateDiff function rounds to the next year. If a person's birthday has not 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 has not, 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

See Also

Working with Dates and Times | The Date Data Type | Getting the Current Date and Time | Formatting a Date | Date Delimiters | Assembling a Date | Getting Part of a Date | Adding and Subtracting Dates