none
Access 2010 - Calculating a persons age RRS feed

  • 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

Answers

  • 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
    

    Please study the in-line comments for the details.


    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.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates the use of a number of age related functions.


    Ken Sheridan, Stafford, England

    Wednesday, January 15, 2020 6:38 PM