First Published 18 Apr 2022 Last Updated 28 June 2023
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
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
Additional Info - 26 June 2023:
1. Leap Year Birthdays
Some time ago I had a fairly lengthy exchange with another MVP at Bytes.com where we disagreed about when someone born on Feb 29 in a leap year would officially become one year older in non-leap years and how Access should treat such calculations.
To summarise that exchange, his view was that Feb 28 should then be used as their birthdate was the last day of Feb whereas I stated that should occur on 1 March as someone is only considered to be one year older once the calendar reaches their birth date. Both viewpoints have some validity.
The reality is for legal purposes, each country makes a choice about which to use. So neither of us was necessarily completely correct in terms of the law.
2. Korean Age
I read an interesting article today about ‘Korean age’: South Koreans become younger under new age-counting law - BBC News
I’m very thankful that I never had to calculate age the traditional South Korean way where people were considered to be age 1 at birth and became one year older on 1 Jan each year. So, on that system, someone born on 31 Dec 2022 was aged 2 on 1 Jan 2023!
Thankfully, the old South Korean system has just been scrapped.
3. More Date/Time Functions
Fellow MVP, Gustav Brock, has a GitHub repository with a wide range of Date/Time functions. See VBA.Date
Feedback
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 28 June 2023
|
Return to Code Samples Page
|
Return to Top
|