Code Samples for Businesses, Schools & Developers

First Published 18 Apr 2022                               Last Updated 8 May 2022


A question that comes up frequently in online forums is how to calculate a person's age in Access.
For example, see this thread at Access World Forums: Age Calculation or this one at Microsoft Tech Community: Calculating Age in Access

A wide variety of solutions are available; some better than others
This article covers several solutions which you can use depending on the degree of accuracy required.

First of all, lets consider two poor solutions:

One very simple approach just subtracts the birth year from the current year

Public Function Age(DOB As Date)

      Year(Date) - Year(DOB)

End Function


This works if the person has already had their birthday this year but gives an incorrect answer if not.
For example, if the current date is 17 April 2022:
a)   DOB: 31 Mar 2012:     Year(#2022-04-17#)-Year(2012-03-31#)=2022-2012 = 10       CORRECT
b)   DOB: 31 Sept 2012:     Year(#2022-04-17#)-Year(2012-09-31#)=2022-2012 = 10       INCORRECT - should be 9

Another very simple, but also unreliable, calculation uses the average number of days in a year, allowing for a leap year every 4 years i.e. 365.25.

Public Function Age(DOB As Date)

      Age = Int((Date - DOB) / 365.25)

End Function


Errors can most easily be seen by testing on a person's birthday
The following tests were done on #4/17/2022# for people with a birthday on that date

InaccurateAgeCalc
Alternate values above are incorrect

NOTE: A more accurate value for the average days in a year is 365.242199.
Using that figure would give much greater reliability but occasional errors will still occur

For calendar dates, an additional leap year correction is done every 100/400 years.
A centurial year is only a leap year if it is also divisible by 400. For example, 2000 was a leap year but 1900 & 2100 are not.
That adjustment brings the average length of the year to 365.2425 days, which is very close to the actual number.

All the remaining functions listed below do work reliably
In all cases, valid solutions include a check on whether the person has already had their birthday in the current year.

NOTE: All dates MUST be in mm/dd/yyyy or yyyy-mm-dd format



1.   Age in Years

Function AgeYr(DOB As Date) As Integer

      'age in years
      AgeYr = DateDiff("yyyy", DOB, Date) + (Format(DOB, "mmdd") > Format(Date, "mmdd"))

End Function


Typical usage: AgeYr(#1982-07-27#) = 39       or AgeYr(#07/27/1982#) = 39

The next function calculates age between 2 dates e.g. birth & death dates

Function AgeYears(datDate1 As Date, datDate2 As Date) As Integer

      'age in years between 2 dates
      If datDate1 <= datDate2 Then
            AgeYears = DateDiff("yyyy", datDate1, datDate2) + (Format(datDate1, "mmdd") > Format(datDate2, "mmdd"))
      Else
            AgeYears = Abs(DateDiff("yyyy", datDate1, datDate2) + (Format(datDate1, "mmdd") > Format(datDate2, "mmdd"))) - 1
      End If

End Function


Typical usage: AgeYears(#1952-07-27#, #2008-04-13#) = 55



2.   Age in Years and Months

Public Function AgeYearMonth(DOB As Date)

     'calculate age in years & months
      AgeYearMonth = DateDiff("yyyy", [DOB], Date) + (Format([DOB], "mmdd") > Format(Date, "mmdd")) & "yr " & _
                  (Month(Date) - Month([DOB]) + 12 + (Day(Date) < Day([DOB]))) Mod 12 & "m"


End Function


Typical usage: AgeYearMonth(#1965-08-14#) = 56yr 8m (on 17 Apr 2022)



3.   Age in Years, Months And Days

Most age functions only deal with years & months
The next function that also includes days & appears to work perfectly:

NOTE: Modified 8 May 2022 to make end date argument optional

Public Function CalcAge(dteDOB As Date, Optional dteEnd As Date) As String

      'years, months & days
      Dim intYears As Integer, intMonths As Integer, intDays As Integer

      'If no end date supplied, use current date as default
      If Nz(dteEnd, 0) = 0 Then dteEnd = Date

      intMonths = DateDiff("m", dteDOB, dteEnd)
      intDays = DateDiff("d", DateAdd("m", intMonths, dteDOB), dteEnd)

      If intDays < 0 Then
            intMonths = intMonths - 1
            intDays = DateDiff("d", DateAdd("m", intMonths, dteDOB), dteEnd)
      End If

      intYears = intMonths \ 12
      intMonths = intMonths Mod 12

      CalcAge = intYears & " Years, " & intMonths & " Months And " & intDays & " Days"

      'Debug.Print CalcAge

End Function


Typical usage: CalcAge(#1965-08-14#, #2022-04-17#) = 56 Years, 8 Months And 3 Days (on 17 Apr 2022)



UPDATE 4 May 2022

4.   Age in Years, Months, Days, Hours, Minutes And Seconds

I added this function in response to a request from David Nealey on LinkedIn.

NOTE:
David spent much of his working life as an astrogeologist so is used to thinking in terms of very precise time intervals - both large & small.
As astrogeologists always say: Space Rocks!

I decided to 'humour' David's request as adding the extra level of precision was easy enough to do . . . but of course the result is out of date 1 second later!

I decided to name the function NealeyAge 😁

NOTE: Modified 8 May 2022 to make end date argument optional

Public Function NealeyAge(dteDOB As Date, Optional dteEnd As Date) As String

      'years, months, days, hours, minutes and seconds!
      'named after David Nealey who wanted to know ages to the nearest second!

      Dim intYears As Integer, intMonths As Integer, intDays As Integer, bytHour As Byte, bytMin As Byte, bytSec As Byte
      Dim lngTimeDiff As Double

      'If no end date supplied, use Now as default
      If Nz(dteEnd, 0) = 0 Then dteEnd = Now

      'Get time difference part in seconds (86400 seconds in a day)
      lngTimeDiff = CLng(86400 * (GetRemainder(dteEnd) - GetRemainder(dteDOB)))

      intMonths = DateDiff("m", dteDOB, dteEnd)

      intDays = DateDiff("d", DateAdd("m", intMonths, dteDOB), dteEnd)

      If intDays < 0 Then
            intMonths = intMonths - 1
            intDays = DateDiff("d", DateAdd("m", intMonths, dteDOB), dteEnd)
      End If

      intYears = intMonths \ 12
      intMonths = intMonths Mod 12

      'adjust if time difference is negative
      If lngTimeDiff < 0 Then
            intDays = intDays - 1 'subtract a day
            lngTimeDiff = 86400 + lngTimeDiff 'recalculate number of seconds
      End If

      NealeyAge = intYears & " Years, " & intMonths & " Months, " & intDays & " Days, " & GetTimeDifference(lngTimeDiff)

End Function

======================================

Function GetRemainder(dteDate As Date)

      'returns the decimal part of an expression
      GetRemainder = CDbl(dteDate) - Int(CDbl(dteDate))

End Function

======================================

Function GetTimeDifference(lngTimeDiff)

      'creates a text string for the time part of the calculation
      Select Case lngTimeDiff

      Case Is < 60
            GetTimeDifference = "0 Hours, 0 Minutes And " & lngTimeDiff & " Seconds"

      Case Is < 3600
            GetTimeDifference = "0 Hours, " & (lngTimeDiff \ 60) & " Minutes And " & (lngTimeDiff Mod 60) & " Seconds"

      Case Is < 86400
            GetTimeDifference = (lngTimeDiff \ 3600) & " Hours, " & ((lngTimeDiff Mod 3600) \ 60) & " Minutes And " & (lngTimeDiff Mod 60) & " Seconds"

      End Select

End Function



Typical usage:
NealeyAge(#5/15/1952 7:23:51 PM#, #5/4/2022 11:59:47 PM#) = 69 Years, 11 Months, 19 Days, 4 Hours, 35 Minutes And 56 Seconds



UPDATES:
1.   4 May 2022 - added NealeyAge function
2.   8 May 2022 - end date argument in CalcAge & NealeyAge functions now optional with default as current date / now respectively




I hope that one or more of the valid functions given above will be useful to other developers
Please let me know if you discover any specific example where a function gives an incorrect answer



Colin Riddington           Mendip Data Systems                 Last Updated 8 May 2022



Return to Code Samples Page




Return to Top