Age in decimal value

Question

Hi

How can I get a person's age from DOB with decimal values such as 23.1 years? Basically I need to know as soon as a person is over a given age even if by a day. With integer age it doesn't work out.

Thanks

Regards

Answer 1

You might like to take a look at Age.zip in my public databases folder at:

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

This little demo file contains a number of age related functions , including the following:

Public Function GetAgeMonthsDays(varDoB As Variant, blnShowYears As Boolean, Optional varAgeAt As Variant) As Variant

Dim intYears As Integer
Dim intMonths As Integer
Dim intDays As Integer

If Not IsNull(varDoB) Then
    If IsMissing(varAgeAt) Or IsNull(varAgeAt) Then varAgeAt = VBA.Date

    intMonths = DateDiff("m", varDoB, varAgeAt)

    If Day(varDoB) > Day(varAgeAt) Then
        intMonths = intMonths - 1
        intDays = DateDiff("d", DateAdd("m", intMonths, varDoB), varAgeAt)
    Else
        intDays = Day(varAgeAt) - Day(varDoB)
    End If

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

    If blnShowYears Then
        intYears = intMonths \ 12
        intMonths = intMonths Mod 12
        GetAgeMonthsDays = intYears & ":" & Format(intMonths, "00") & ":" & Format(intDays, "00")
    Else
        GetAgeMonthsDays = Format(intMonths, "00") & ":" & Format(intDays, "00")
    End If

End If

End Function

Normally this function returns a person's age in the format yy:mm:dd, but if the following lines:

        GetAgeMonthsDays = intYears & ":" & Format(intMonths, "00") & ":" & Format(intDays, "00")
    Else
        GetAgeMonthsDays = Format(intMonths, "00") & ":" & Format(intDays, "00")

are changed to:

        GetAgeMonthsDays = Val(intYears & Format(intMonths, "00") & Format(intDays, "00"))
    Else
        GetAgeMonthsDays = Val(Format(intMonths, "00") & Format(intDays, "00"))

it will return the age as a number in the chronologically sortable format yymmdd.

You can then compare the returned values to determine if one is greater than another, e.g. to determine if I am more than 75 years, 3 months and 10 days old:

? GetAgeMonthsDays(#1946-11-01#,True) > 750310
True

whereas:

? GetAgeMonthsDays(#1946-11-01#,True) > 750320
False

shows that I am not currently older than 75 years, 3 months and 20 days old.

Note that the above function calls the IsLeapDate function from the same basAgeStuff module in the demo, so you'd need to include that in your database also.

Answer 2

If you want to check the age, consider this approach:

Dim dob As Date
dob = DateSerial(1998, 7, 30)

If DateAdd("yyyy", 23, dob) < DateValue(Now) Then
    MsgBox "Over 23 years"
End If