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)


        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:



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.




Steve Schapel