# Access 2010 - Calculating a persons age

• ### Question

• I have a database that keeps track of people. I have a field called Date_of_Birth. I now want to put an unbound textbox on my form with code that calculates the persons age and displays their age in that text box.

Any ideas ?

Steven Schuyler Berkeley, California USA
Monday, November 21, 2011 7:39 PM

• If you want to calculate the age in years:

```=Year(Date())-Year([Date_of_Birth]) + (Month(Date())<Month([Date_of_Birth])) + (Month(Date())=Month([Date_of_Birth]) And Day(Date())<Day([Date_of_Birth]))

```

Regards, Hans Vogelaar
Monday, November 21, 2011 7:48 PM
• That simple formula that will work perfectly for most purposes. It fails to take into account that 1900 and 2100 aren't leap years, but that will seldom be a problem.
Regards, Hans Vogelaar
Monday, November 21, 2011 8:56 PM
• Set the controlsource to =Int(DateDiff("yyyy",[Date_of_Birth],Now()))

Eric Williams
Monday, November 21, 2011 8:18 PM
• DateDiff isn't entirely accurate: it simply subtracts the years and doesn't take the day and month into account. For example, if Date_of_Birth is 23-December-2000, DateDiff will return 11 as age today (21-November-2011), although this person's 11th birthday is still more than a month ahead. It simply calculates 2011 - 2000 = 11.
Regards, Hans Vogelaar
Monday, November 21, 2011 8:23 PM
• Hans,

You are correct. Ithink I knew that and forgot it. Thank you for correcting me. In that case wouldn't the following wok just as well?

=Int(DateDiff("d",[Date_of_Birth],Now))/365.25)

(The .25 accounting for leap years which I believe are properly accounted for by DateDiff)

Eric Williams
Monday, November 21, 2011 8:35 PM

### All replies

• If you want to calculate the age in years:

```=Year(Date())-Year([Date_of_Birth]) + (Month(Date())<Month([Date_of_Birth])) + (Month(Date())=Month([Date_of_Birth]) And Day(Date())<Day([Date_of_Birth]))

```

Regards, Hans Vogelaar
Monday, November 21, 2011 7:48 PM
• Set the controlsource to =Int(DateDiff("yyyy",[Date_of_Birth],Now()))

Eric Williams
Monday, November 21, 2011 8:18 PM
• DateDiff isn't entirely accurate: it simply subtracts the years and doesn't take the day and month into account. For example, if Date_of_Birth is 23-December-2000, DateDiff will return 11 as age today (21-November-2011), although this person's 11th birthday is still more than a month ahead. It simply calculates 2011 - 2000 = 11.
Regards, Hans Vogelaar
Monday, November 21, 2011 8:23 PM
• Hans,

You are correct. Ithink I knew that and forgot it. Thank you for correcting me. In that case wouldn't the following wok just as well?

=Int(DateDiff("d",[Date_of_Birth],Now))/365.25)

(The .25 accounting for leap years which I believe are properly accounted for by DateDiff)

Eric Williams
Monday, November 21, 2011 8:35 PM
• That simple formula that will work perfectly for most purposes. It fails to take into account that 1900 and 2100 aren't leap years, but that will seldom be a problem.
Regards, Hans Vogelaar
Monday, November 21, 2011 8:56 PM
• I literally tried about 10 different "Age" formulas for 2007 and they all failed when the birthday's date got too close to today's date. This one works perfectly for me even when I tested the DOB falling the day after today or today's date. Thank you.
Wednesday, May 22, 2019 2:54 AM
• If you need a function that not even seldom fails, but never fails, it must use DateAdd as here:

```Public Function AgeSimple( _
ByVal datDateOfBirth As Date) _
As Integer

' Returns the difference in full years from datDateOfBirth to current date.
'
' Calculates correctly for:
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of years to dates of Feb. 29.
' when the resulting year is a common year.
' After an idea of Markus G. Fischer.
'
' 2007-06-26. Cactus Data ApS, CPH.

Dim datToday  As Date
Dim intAge    As Integer
Dim intYears  As Integer

datToday = Date
' Find difference in calendar years.
intYears = DateDiff("yyyy", datDateOfBirth, datToday)
If intYears > 0 Then
' Decrease by 1 if current date is earlier than birthday of current year
' using DateDiff to ignore a time portion of datDateOfBirth.
intAge = intYears - Abs(DateDiff("d", datToday, DateAdd("yyyy", intYears, datDateOfBirth)) > 0)
End If

AgeSimple = intAge

End Function
```

Gustav Brock

Thursday, May 23, 2019 7:42 AM
• Allen Browne as nice function at Age()

```Function Age(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose:   Return the Age in years.
'Arguments: varDOB = Date Of Birth
'           varAsOf = the date to calculate the age at, or today if missing.
'Return:    Whole number of years.
Dim dtDOB As Date
Dim dtAsOf As Date
Dim dtBDay As Date  'Birthday in the year of calculation.

Age = Null          'Initialize to Null

'Validate parameters
If IsDate(varDOB) Then
dtDOB = varDOB

If Not IsDate(varAsOf) Then  'Date to calculate age from.
dtAsOf = Date
Else
dtAsOf = varAsOf
End If

If dtAsOf >= dtDOB Then      'Calculate only if it's after person was born.
dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
Age = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
End If
End If
End Function```

Thursday, May 23, 2019 2:15 PM
• I use this one which allows for returning age on a date other than today.

Public Function CalcAge(DOB As Variant, Optional vDate As Variant) As Variant

'pass vDate to find age on date other than today

If IsDate(vDate) = False Then
vDate = Date
End If

If IsDate(DOB) = True Then
CalcAge = DateDiff("yyyy", DOB, vDate) + (DateSerial(Year(vDate), Month(DOB), Day(DOB)) > vDate)

Else
CalcAge = Null
End If

End Function

Dave Thompson

Thursday, May 23, 2019 2:15 PM
• The net - and this thread too - is flooded with more or less smart methods, that get it nearly right but no more.

But there is no way around using DateAdd to get it completely right. You may not need it for your birthday greetings cards, but in many businesses - like insurance and health care - an age calculation must be 100% reliable.

This example provides that at any given date including today:

```Public Function Years( _
ByVal datDate1 As Date, _
ByVal datDate2 As Date, _
Optional ByVal booLinear As Boolean) _
As Integer

' Returns the difference in full years between datDate1 and datDate2.
'
' Calculates correctly for:
'   negative differences
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'   negative date/time values (prior to 1899-12-29)
'
' Optionally returns negative counts rounded down to provide a
' linear sequence of year counts.
' For a given datDate1, if datDate2 is decreased step wise one year from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If booLinear is False, the sequence will be:
'   3, 2, 1, 0,  0, -1, -2
' If booLinear is True, the sequence will be:
'   3, 2, 1, 0, -1, -2, -3
'
' If booLinear is False, reversing datDate1 and datDate2 will return
' results of same absolute Value, only the sign will change.
' This behaviour mimics that of Fix().
' If booLinear is True, reversing datDate1 and datDate2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().

' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of years to dates of Feb. 29.
' when the resulting year is a common year.
'
' 2007-06-22. Cactus Data ApS, CPH.

Dim intDiff   As Integer
Dim intSign   As Integer
Dim intYears  As Integer

' Find difference in calendar years.
intYears = DateDiff("yyyy", datDate1, datDate2)
' For positive resp. negative intervals, check if the second date
' falls before, on, or after the crossing date for a full 12 months period
' while at the same time correcting for February 29. of leap years.
If DateDiff("d", datDate1, datDate2) > 0 Then
intSign = Sgn(DateDiff("d", DateAdd("yyyy", intYears, datDate1), datDate2))
intDiff = Abs(intSign < 0)
Else
intSign = Sgn(DateDiff("d", DateAdd("yyyy", -intYears, datDate2), datDate1))
If intSign <> 0 Then
' Offset negative count of years to continuous sequence if requested.
intDiff = Abs(booLinear)
End If
intDiff = intDiff - Abs(intSign < 0)
End If

' Return count of years as count of full 12 months periods.
Years = intYears - intDiff

End Function
```

Gustav Brock

Thursday, May 23, 2019 2:33 PM
• But there is no way around using DateAdd to get it completely right.

Hi Gustav,

This is the routine that I use, without DateAdd:

```Function Leeftijd(from_date, to_date)
Dim decr As Integer

If (Not IsDate(from_date)) Then Exit Function
If (Not IsDate(to_date)) Then Exit Function

Select Case Month(from_date) - Month(to_date)
Case Is > 0: decr = 1
Case 0: If (Day(from_date) > Day(to_date)) Then decr = 1
End Select

Leeftijd = Year(to_date) - Year(from_date) - decr

End Function
```

Imb.

Thursday, May 23, 2019 9:43 PM
• Well, it probably fits your purpose, but it can't be used for critical implementations, as it fails for the leaplings.

Gustav Brock

Friday, May 24, 2019 6:05 AM
• Well, it probably fits your purpose, but it can't be used for critical implementations, as it fails for the leaplings.

Gustav Brock

Hi Gustav,

That is remarkable, because in my opinion it handles the leapdays in the right way.

Can you give me an example where the result is not correct?

Imb.

Friday, May 24, 2019 7:28 AM
• Yes, here is an example for validating:

```DateAdd("yyyy", 19, #2000/02/29#) -> #2019/02/28#
Years(#2000/02/29#, #2019/02/28#) -> 19
```

Gustav Brock

Friday, May 24, 2019 7:42 AM
• ..............because in my opinion it handles the leapdays in the right way.

I'm afraid not.  With Gustav's example:

? Leeftijd(#2000-02-29#, #2019-02-28#)
18

I use the following:

Public Function GetAge(varDoB As Variant, Optional varAgeAt As Variant) As Variant

If IsMissing(varAgeAt) Then varAgeAt = VBA.Date

GetAge = DateDiff("yyyy", varDoB, varAgeAt) - _
IIf(Format(varAgeAt, "mmdd") < Format(varDoB, "mmdd"), 1, 0)

' adjust for leap year if necessary
If IsLeapDate(varDoB) And Month(varAgeAt) = 2 And Day(varAgeAt) = 28 And Not IsLeapDate(varAgeAt + 1) Then
GetAge = GetAge + 1
End If

End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function IsLeapDate(ByVal dtmDate As Date) As Boolean

If Month(dtmDate) = 2 And Day(dtmDate) = 29 Then
IsLeapDate = True
End If

End Function

Which returns the correct value:

? GetAge(#2000-02-29#, #2019-02-28#)
19

I haven't tried Gustav's function, but I've no doubt it's also reliable.

Ken Sheridan, Stafford, England

Friday, May 24, 2019 11:15 AM
• Oy! Lots of suggestions for a very simple function. This one is accurate. It's been used in a clinical application for years.

```Public Function CalcAge(DOB As Variant)
'Purpose  : Calculate age
'DateTime : 3/18/2000 10:50
'Author   : Bill Mosca
On Error Resume Next

CalcAge = DateDiff("yyyy", DOB, Now()) _
+ Int(Format(Now(), "mmdd") < Format(DOB, "mmdd"))

End Function```

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Friday, May 24, 2019 6:02 PM
• Yes, here is an example for validating:

```DateAdd("yyyy", 19, #2000/02/29#) -> #2019/02/28#
Years(#2000/02/29#, #2019/02/28#) -> 19```

Hi Gustav,

There is indeed a difference. "Years" results in 19, and "Leeftijd" results in 18.

To get 19 as result you must add the synthetical rule that when a year does not have a leap day, then the DoB of 29-feb will be converted to 28-feb.

With 18 as result, you do not make any new rule. On 28-feb the leaper has still an age of 18, and the next day, 1-mar, the leaper is 19 years old.

It all depends on that interpretation. Most leapers will have their party on 28-feb, but that is different from the question  how to calculate the age with "with 100% validity".

I have copied a piece of tekst from the Dutch Wikipedia about Leeftijd:

Bij mensen wordt gerekend vanaf de geboorte, en wordt de leeftijd meestal gegeven in gehele levensjaren. Een nieuw levensjaar (te onderscheiden van onder meer kalenderjaar en schooljaar) begint met de verjaardag, om 0 uur, ongeacht de tijd van geboorte, maar als men op 29 februari geboren is begint het nieuwe levensjaar in niet-schrikkeljaren op 1 maart. Voor baby's wordt de leeftijd wel gerekend in weken of maanden. Als dit van belang is, kan ook de leeftijd van oudere kinderen en volwassenen in dagen gegeven worden, waarbij de geboortedag als de eerste dag van bestaan geteld wordt.

with the most important line:

… but when one is born on 29 february, the new live-year starts, in non-leap years, on 1 march.

That means that the function Years is not 100% valid under ALL conditions, but only under the assumption that the definition of DoB will be 28-feb for leapers in non-leap years. And this can be law or situation dependant.

Very remarkable is that Bill's results, with very long experience in clinical applications, matches the results of "Leeftijd".

Imb.

Friday, May 24, 2019 8:14 PM
• I have only met one leapling, and she insisted to have her birthday on the last day of February. If born in February, your birthday does not fall in March. Also, in business and accounting, ultimo means ultimo, not primo.

That's probably the reasoning while DateAdd was decided to behave as it does, and my Age function reflects that.

Of course, law and business rules can declare other methods, and you will have to follow. An extreme example is banking where every month is defined to have 30 days, leap year or common year.

Gustav Brock

Saturday, May 25, 2019 8:47 AM
• Oy! Lots of suggestions for a very simple function. This one is accurate. It's been used in a clinical application for years.

```Public Function CalcAge(DOB As Variant)
'Purpose  : Calculate age
'DateTime : 3/18/2000 10:50
'Author   : Bill Mosca
On Error Resume Next

CalcAge = DateDiff("yyyy", DOB, Now()) _
+ Int(Format(Now(), "mmdd") < Format(DOB, "mmdd"))

End Function```

Hi Bill,

I like your one-liner very much! Beautiful.

Imb.

Saturday, May 25, 2019 9:25 AM
• I like this one too, Bill!

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Saturday, May 25, 2019 9:59 AM
• For legal purposes, e.g. reaching age of majority, there is no consensus internationally as to when the anniversary of a 29 February birthdate falls in a non-leap year.  In some jurisdictions it falls on 28 February, in others on 1 March.  Like Gustav and Microsoft I think the former is more logically correct, and my functions reflect this.  As I distribute files worldwide, however, I propose to add an additional argument to my function to allow both options.

Ken Sheridan, Stafford, England

Saturday, May 25, 2019 4:58 PM
• For legal purposes, e.g. reaching age of majority, there is no consensus internationally as to when the anniversary of a 29 February birthdate falls in a non-leap year.  In some jurisdictions it falls on 28 February, in others on 1 March.  Like Gustav and Microsoft I think the former is more logically correct, and my functions reflect this.  As I distribute files worldwide, however, I propose to add an additional argument to my function to allow both options.

Hi Ken,

It all depends what kind of logic you want to follow.

The use of the DoB is historical coupled to the day where you want to have your birthday party, and - in that kind of logic - it is more associated with the last day of February then the first day of March, thus more or less emotional driven logic. This is also what I read from Gustav's answer. By the way, I don't think Microsoft has any prevalence, it is "your" use of a function that selects the last day of the same month.

I am very much in favour of the Dutch definition, where you don't take a DAY as reference to calculate the age, but a POINT OF TIME: the starting point of a new year in one's life. For me, this sound "very logical" to happen after February 28 has passed, independant of it being a leapyear or not. In non-leapyears this happens to be March 1, 00:00:00,000000000 …

The illogical part for me in the definition of February 28 as DoB to calculate the age is that - in non-leapyears - the starting point of a new life-year is placed back 24 hours with respect to the case of leapyears.

Nevertheless: for legal purposes the law has to be followed (knowing that laws are not always logical).

Imb.

Saturday, May 25, 2019 6:28 PM
• Thank you Imb and Hans. Compliments from you two experts are high praise.

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Sunday, May 26, 2019 8:35 PM
• A few questions: 1) will this work with Access 2016? 2) what if I need months and days as well? 3) will you specify what are column headers or text box or labels in the form? 4) where do you embed this macro? Query? Control source? Thanks in advance! Emily
Wednesday, January 15, 2020 1:23 AM
• 1. Yes

2. You can use these functions:

```' Returns the difference in full months from DateOfBirth to current date,
' optionally to another date.
' Returns by reference the difference in days.
' Returns zero if AnotherDate is earlier than DateOfBirth.
'
' Calculates correctly for:
'   leap Months
'   dates of 29. February
'   date/time values with embedded time values
'   any date/time value of data type Date
'
' DateAdd() is, when adding a count of months to dates of 31th (29th),
' used for check for month end as it correctly returns the 30th (28th)
' when the resulting month has 30 or less days.
'
' 2015-11-24. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function AgeMonthsDays( _
ByVal DateOfBirth As Date, _
Optional ByVal AnotherDate As Variant, _
Optional ByRef Days As Integer) _
As Long

Dim ThisDate    As Date
Dim Months      As Long
Dim BirthDay    As Integer
Dim ThisDay     As Integer

If IsDateExt(AnotherDate) Then
ThisDate = CDate(AnotherDate)
Else
ThisDate = Date
End If

' Find difference in calendar Months.
Months = DateDiff("m", DateOfBirth, ThisDate)
If Months < 0 Then
Months = 0
Else
If Months > 0 Then
' Decrease by 1 if current date is earlier than birthday of current year
' using DateDiff to ignore a time portion of DateOfBirth.
If DateDiff("d", ThisDate, DateAdd("m", Months, DateOfBirth)) > 0 Then
Months = Months - 1
End If
End If
' Find difference in days.
Days = DateDiff("d", DateAdd("m", Months, DateOfBirth), ThisDate)
End If

AgeMonthsDays = Months

End Function

' Formats the output from AgeMonthsDays.
'
' 2015-11-24. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function FormatAgeYearsMonthsDays( _
ByVal DateOfBirth As Date, _
ByVal AnotherDate As Date) _
As String

Const MonthsPerYear As Integer = 12

Dim Years           As Integer
Dim Months          As Integer
Dim Days            As Integer

Months = AgeMonthsDays(DateOfBirth, AnotherDate, Days)
Years = Months \ MonthsPerYear
Months = Months Mod MonthsPerYear

FormatAgeYearsMonthsDays = Years & " years, " & Months & " months, " & Days & " days"

End Function```

3. That's up to you

4. Anywhere where you can use a function

Gustav Brock

Wednesday, January 15, 2020 8:24 AM
• A few questions: 1) will this work with Access 2016? 2) what if I need months and days as well? 3) will you specify what are column headers or text box or labels in the form? 4) where do you embed this macro? Query? Control source? Thanks in advance! Emily
You might also like to take a look at Age.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.