Home

Deriving Age from Date of Birth, Allowing for Leap Years

First the Basics

I guess it’s a common mistake, especially for those new to database work, to store the Age of a person in a database table.

Of course, the main reason that this is a bad idea is that age changes, so keeping the stored data accurate in such a case becomes a near impossibility.

The usual advice, therefore, is to store the Date of Birth (which does not vary), and calculate the Age whenever it is required for the purposes of the database application.

A Common Expression

A search of the internet, newsgroups, etc. will reveal plenty of different approaches to calculating Age, based on Date of Birth. Some are more accurate than others.

A commonly recommended expression, for example in the context of a calculated field in a query, is this:

Age: DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmdd"))

… for the current age. Or like this for the age at a specified date:

Age: DateDiff("yyyy",[DOB],[SpecifiedDate])+(Format([DOB],"mmdd")>Format([SpecifiedDate],"mmdd"))

This expression allows for the situation where the birthday in the current year has not yet happened. It is a very useful formula, and will be accurate in almost all circumstances, including historical dates.

Birthday 29 February

In fact, as far as I know, the only time it won’t be accurate is in the case of a 29 February birthday, and the age is being evaluated on 28 February in a non leap year. So, it’s pretty rare.

Nevertheless, the expression can be adjusted to cater to this special case:

DateDiff("yyyy",[DOB],Date())+((Format([DOB]+(Format([DOB],"mmdd")="0229" And Format(Date(),"mmdd")="0228" And Format(Date()+1,"mmdd")<>"0229"),"mmdd"))>Format(Date(),"mmdd"))

DateDiff("yyyy",[DOB],[SpecifiedDate])+((Format([DOB]+(Format([DOB],"mmdd")="0229" And Format([SpecifiedDate],"mmdd")="0228" And Format([SpecifiedDate]+1,"mmdd")<>"0229"),"mmdd"))>Format([SpecifiedDate],"mmdd"))

Not too difficult to use something like this, as is, whenever required within your database. But a bit of a mouthful, I admit.

User Defined Function

Nevertheless, we can extend this same concept, and create a function that does the same thing.

In a Standard Module, copy the following code:

Public Function FindAge(DOB As Date, Optional DateFrom As Variant = 0) As Integer

Dim lEvalDOB As Long

Dim lEvalFrom As Long

Dim iAdjustLeap As Integer

Dim iAdjustMonth As Integer

Dim iAge As Integer

' define dates to use for evaluation of Age

' convert to Long Integer to allow for international date formats

If DateFrom = 0 Then

' assume current date

lEvalFrom = CLng(Date)

Else

lEvalFrom = CLng(DateFrom)

End If

lEvalDOB = CLng(DOB)

' if evaluation date is before date of birth, assume to return age of 0

If lEvalDOB > lEvalFrom Then

lEvalDOB = lEvalFrom

End If

' get the baseline difference in whole years

iAge = DateDiff("yyyy", lEvalDOB, lEvalFrom)

' adjust date to evaluate if date of birth is leap day

If Format(lEvalDOB, "mmdd") = "0229" Then

If Format(lEvalFrom, "mmdd") = "0228" And Format(lEvalFrom + 1, "mmdd") <> "0229" Then

iAdjustLeap = 1

End If

End If

lEvalDOB = lEvalDOB - iAdjustLeap

' adjust for current year birthday not reached

If Format(lEvalDOB, "mmdd") > Format(lEvalFrom, "mmdd") Then

iAdjustMonth = 1

End If

iAge = iAge - iAdjustMonth

' return function value

FindAge = iAge

End Function

Now, you can use this within the application. For example, for the current age:

FindAge([DOB])

For the age at another specified date:

FindAge([DOB],[SpecifiedDate])

… with the appropriate syntax adjustments, depending on whether it is being used in a calculated field in a query, or in the Control Source of a calculated control on a form or report, or within a VBA procedure.

Disclaimer

This code makes the assumption that if the date where the age is being evaluated is before the date of birth, the Age will be 0, in other words we will not record a negative number as the age. This is likely correct for most applications, but there may be specialised business scenarios where this assumption is not the correct one.

The code also assumes that for a 29 February date of birth, the person’s birthday will be regarded as 28 February in non leap years. I understand that this model is not necessarily universally adhered to.

Conclusion

The above is an outline of one approach to derive Age in years accurately, based on Date of Birth.

Steve Schapel