Convert Date of Birth to Age.

[Petemush]

Is there a built in function to convert Date of Birth to age? I've written one myself but it's not particularly swift.

Public Function DateToAge(DOB As Date) As Integer

Dim dOBM, dOBD, todayM, todayD, age As Integer

dOBM = DatePart("m", DOB)
dOBD = DatePart("d", DOB)
todayM = DatePart("m", Date)
todayD = DatePart("d", Date)
age = (DateDiff("yyyy", DOB, Date)) - 1

If dOBM <= todayD Then
If dOBD <= todayD Then
age = DateDiff(&quot;yyyy&quot;, DOB, Date)
End If
End If

DateToAge = age
End Function

If there isn't a built in function can anyone improve on mine?

Thanks in Advance

Pete

 

[Bigpapou]

I don't think there's a built in function but you're doing to many test. Since DateDiff() will be < 0 if DOB < Date I guess you could just do this:

Public Function DateToAge(DOB As Date) As Integer

if (DateDiff(&quot;d&quot;, DOB, Date)) < 0 then
‘Error handling code
else
age = (DateDiff(&quot;yyyy&quot;, DOB, Date)) - 1
end if

DateToAge = age
End Function

Hope this will help.

 

[Petemush]

Thanks,

I've just noticed though that my code is incorrect.

The If clauses should read,

If dOBM <= todayM Then
If dOBD <= todayD Then

Also just thought about the implications of this if the Date on the users computer is incorrect.

 

[MichaelRed]

Public Function basDOB2Age(DOB As Date, Optional AsOf As Date = -1) As Integer

    'Dob is just the date of Birth
    'AsOf is an optional date to check - as in examples 1 & 2
    'Otherwise, the DOB is checked against the Current Date

    'Michael Red 12/15/2001
    'To Calculate Age from Date of Birth
    
    'Sample Useage:
    '? basDOB2Age(#8/21/1942#, #8/21/2022#)
    '80

    '? basDOB2Age(#8/21/1942#, #8/20/2022#)
    '79

    '? basDOB2Age(#8/21/1942#)
    '59


    Dim tmpAge As Integer       'Simple DateDiff w/o Birthday correction
    Dim BrthDayCorr As Boolean  'BirthDay Before or After date in question

    If (AsOf = -1) Then         'Check for (Optional Date to Check against)
        AsOf = Date             'If Not Supplied, Assume Today
    End If

    tmpAge = DateDiff("YYYY", DOB, AsOf)        'Just the Years considering Jan 1, Mam
    BrthDayCorr = DateSerial(Year(AsOf), Month(DOB), Day(DOB)) > AsOf   'Check This Year

    basDOB2Age = tmpAge + BrthDayCorr           'Just Years and Correction
    
End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[scriverb]

The following statement will handle the calculation of age in all situations:

IIf(DatePart(&quot;m&quot;,[tblYourTable]![BIRTH_DATE])=DatePart(&quot;m&quot;,Date()) And DatePart(&quot;d&quot;,[tblYourTable]![BIRTH_DATE])=DatePart(&quot;d&quot;,Date()), CInt((DateDiff('d',[tblYourTable]![BIRTH_DATE],Date())/365.25)), CInt((DateDiff('d',[tblYourTable]![BIRTH_DATE],Date())/365.25)-0.5))

Check out the thread: thread701-281348

There is a detailed explaination of how this works.

I have tested this on many combinations of birthdates and it handles all situations well.

Bob Scriver

 

[Quehay]

Refer to Microsoft Knowledge Base Article: Q100136

It offers two ways of doing this.