Chronological age

[Ness]

I have a database that I need to calculate someone's chronological age for an
appointment date, does anyone know what the formula would be

 

[Jim Burke in Novi]

One way to do it:

Age = Year(AppointmentDate) - Year(DOB)
DOBMonth = Month(DOB)
ApptMonth = Month(AppointmentDate)
If ApptMonth < DOBMonth Or _
(ApptMonth = DOBMonth And Day(AppointmentDate) < Day(DOB)) Then
Age = Age - 1
End If

 

[Stuart McCall]

I have a database that I need to calculate someone's chronological age for
an
appointment date, does anyone know what the formula would be

Paste this function into a standard module:


Public Function Age(Bdate As Date, DateToday As Date) As Integer
'Returns age in years between 2 dates
If Month(DateToday) < Month(Bdate) Or _
(Month(DateToday) = Month(Bdate) And _
Day(DateToday) < Day(Bdate)) Then
Age = Year(DateToday) - Year(Bdate) - 1
Else
Age = Year(DateToday) - Year(Bdate)
End If
End Function

Use it like this: Debug.Print Age("01/01/1980", Date)

Result: 27

 

[Klatuu]

A very straight forward function you may want to keep in a standard module:

Public Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday

Age = DateDiff("yyyy", Bdate, DateToday) - IIf(Format(Bdate, "mmdd") > _
Format(DateToday, "mmdd"), 1, 0)
End Function