Calculate age between two dates

[nducamara]

I would like to calculate the age between two dates i.e Date of Birth and
today. I would like to display the age on my report. I am using Office 2003.
How do I go about it?

 

[John Spencer]

You can use a custom VBA function

Public Function fAge(dtmDOB, Optional dtmDate)
'Returns the Age in years, for dtmDOB.
'Age calculated as of dtmDate, or as of today if dtmDate is missing.

'If as of date not passed then set to today's date
If Not IsDate(dtmDate) Then dtmDate = Date

If IsDate(dtmDOB) Then 'If date passed, then calculate age
fAge = DateDiff("yyyy", dtmDOB, dtmDate) + _
(DateSerial(Year(dtmDate), Month(dtmDOB), Day(dtmDOB)) > dtmDate)
Else
fAge = Null
End If

End Function

OR one of the following expressions should be helpful
'Fails if DOB is null
Int(Format(Date(), "yyyy.mmdd") - Format([DOB], "yyyy.mmdd"))

'Returns Null if DOB is Null
DateDiff("yyyy",DOB,Date()) + Int(Format(DOB,"mmdd") > Format(Date(),"mmdd"))

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Klatuu]

This is the one I use:

Public Function CalcAge(Bdate As Variant, Optional DateToday As Variant) As
Integer
Dim dtmToDate As Date

' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday
' Returns 0 if Bdate is Null
' Uses current date if DateToday is missing or Null

If IsNull(Bdate) Then
CalcAge = 0
Else
If IsMissing(DateToday) Then
dtmToDate = Date
Else
dtmToDate = Nz(DateToday, Date)
End If
If Month(dtmToDate) < Month(Bdate) Or (Month(dtmToDate) = _
Month(Bdate) And Day(dtmToDate) < Day(Bdate)) Then
CalcAge = Year(dtmToDate) - Year(Bdate) - 1
Else
CalcAge = Year(dtmToDate) - Year(Bdate)
End If
End If
End Function

 

[nducamara]

This is the one I use:

Public Function CalcAge(Bdate As Variant, Optional DateToday As Variant) As
Integer
Dim dtmToDate As Date

' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday
' Returns 0 if Bdate is Null
' Uses current date if DateToday is missing or Null

If IsNull(Bdate) Then
CalcAge = 0
Else
If IsMissing(DateToday) Then
dtmToDate = Date
Else
dtmToDate = Nz(DateToday, Date)
End If
If Month(dtmToDate) < Month(Bdate) Or (Month(dtmToDate) = _
Month(Bdate) And Day(dtmToDate) < Day(Bdate)) Then
CalcAge = Year(dtmToDate) - Year(Bdate) - 1
Else
CalcAge = Year(dtmToDate) - Year(Bdate)
End If
End If
End Function