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:
… for the current age. Or like this for the age at a specified date:
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.
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)
lEvalFrom = CLng(DateFrom)
lEvalDOB = CLng(DOB)
' if evaluation date is before date of birth, assume to return age of 0
If lEvalDOB > lEvalFrom Then
lEvalDOB = lEvalFrom
' 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
lEvalDOB = lEvalDOB - iAdjustLeap
' adjust for current year birthday not reached
If Format(lEvalDOB, "mmdd") > Format(lEvalFrom, "mmdd") Then
iAdjustMonth = 1
iAge = iAge - iAdjustMonth
' return function value
FindAge = iAge
Now, you can use this within the application. For example, for the current age:
For the age at another specified date:
… 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.
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.
The above is an outline of one approach to derive Age in years accurately, based on Date of Birth.