date of birth formula (1 Viewer)

[elsiegee]

Hello....

is there a way to automatically calculate someone's age in years from their date of birth which is entered as **/**/** in a form?

I am creating several databases with stats related to people's ages in years, but I don't want to have to keep going back and changing the age every year...it would make my life a lot easier!! thanks

 

[Mile-O]

Function Age(ByVal dteBirth As Date, ByVal dteToday As Date) As Integer

    On Error GoTo Err_Age
   
    ' ensure that date range is not negative
    If dteBirth > dteToday Then
        MsgBox "Error: Date range returns negative value."
        Exit Function ' function will not continue
    End If

    If Month(dteToday) < Month(dteBirth) Or (Month(dteToday) = Month(dteBirth) _
        And Day(dteToday) < Day(dteBirth)) Then
        Age = Year(dteToday) - Year(dteBirth) - 1
    Else
        Age = Year(dteToday) - Year(dteBirth)
    End If
    
Exit_Age:
    Exit Function

Err_Age:
    MsgBox Err.Number & Err.Description
    Resume Exit_Age
    
End Function

 

[raskew]

Here's a similar technique which will deal with the scenario where the dates were reversed.

Function agecount2(pdob As Variant, pdte As Variant) As Integer
'*******************************************
'Name:      agecount2 (Function)
'Purpose:   Determine age in years
'Inputs:    (1) ? agecount2("2/1/86", date())
'           (2) ? agecount2(date(), #1/1/86#)
'Output:    (1) 16; (2) 17
'*******************************************

'allows entry of strings ("10/21/86") or dates (#10/21/86#)
Dim dDOB As Date, dDte As Date, datehold As Date
dDOB = DateValue(pdob)
dDte = DateValue(pdte)

'Reverse the dates if they were input backwards
If dDOB > dDte Then
   datehold = dDOB
   dDOB = dDte
   dDte = datehold
End If

agecount2 = DateDiff("yyyy", dDOB, dDte) + (dDte < DateSerial(Year(dDte), Month(dDOB), day(pdob)))
End Function