Calculating Age

Calculating someone’s age, given that person’s birth date, is a commonplace need in data manipulation. Unfortuntaely, VBA doesn’t give a complete and correct method for calculating a person’s age.

You might be tempted to use this formula:

Age = DateDiff("yyyy", Birthdate, Date)

to calculate age, but this doesn’t quite work. If the birth date hasn’t yet occurred this year, the Age value will be off by 1. For example, imagine your birthday is December 31, and you were born in 1950. If today is October 1, 1997, subtracting the year portions of the two dates (1997 – 1950) would indicate that you were 47 years old. In reality, by the standard way of figuring such things, you’re still only 46.

To handle this discrepancy, the dhAge function in Listing 2.22 not only subtracts one Year portion of the dates from the other, it checks whether the birth date has already occurred this year. If it hasn’t, the function subtracts 1 from the calculation, returning the correct age.

In addition, dhAge allows you to pass an optional second date: the date on which to calculate the age. If you pass nothing for the second parameter, the code assumes you want to use the current date as the ending date. That is, if you use a call like this:

intAge = dhAge(#5/22/59#)

you’ll find the current age of someone born on May 22, 1959. If you call the function like this:

intAge = dhAge(#5/22/59#, #1/1/2000#)

you’ll find out how old the same person will be on the first day of 2000.

Listing 2.22: One Solution for Calculating Age

Function dhAge(dtmBD As Date, Optional dtmDate As Date = 0) _
 As Integer
    ' This procedure is stored as dhAgeUnused in the sample
    ' module.
    Dim intAge As Integer
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    intAge = DateDiff("yyyy", dtmBD, dtmDate)
    If dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), _
     Day(dtmBD)) Then
        intAge = intAge - 1
    End If
    dhAge = intAge
End Function

You might also be tempted to solve this problem by dividing the difference between the two dates, in days, by 365.25. This works for some combinations of dates, but not for all. It’s just not worth the margin of error. The functions presented here are simple enough that they’re a reasonable replacement for the simple division that otherwise seems intuitive.

If you’re looking for the smallest possible solution, perhaps at the expense of readability, you could use the version in Listing 2.23 instead. It relies on the fact that a true expression is equal to the value –1 and a false expression is equal to 0. The function adds –1 or 0 to the year difference, depending on whether the specified birth date has passed.

Listing 2.23: A Second Solution for Calculating Age

Function dhAge(dtmBD As Date, Optional dtmDate As Date = 0) _
 As Integer
    ' Calculate a person's age, given the person's birth date and
    ' an optional "current" date.
    If dtmDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    dhAge = DateDiff("yyyy", dtmBD, dtmDate) + _
     (dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), _
      Day(dtmBD)))
End Function