Hi

Does anybody know trhe function for calculating someones age. I have used a function which uses the following:

DateDiff("d", vFromDate, vToDate)

but it does not calculate the person age entirely acurately. I need the age to be spot on.

Cheers

Cheers
Tony

__________________
Cheers
Tony

This might not be the quickest way to do it, but it works and the code can be amended to do practically anything

Sub agecalc()
Dim dob As Date, calc_date As Date
Dim yb As Integer, mb As Integer, db As Integer
Dim yc As Integer, mc As Integer, dc As Integer
Dim diff1 As Integer, diff2 As Integer

dob = InputBox("Enter date of birth")
calc_date = InputBox("Enter calculation date")

yb = Year(dob)
mb = Month(dob)
db = Day(dob)

yc = Year(calc_date)
mc = Month(calc_date)
dc = Day(calc_date)

'---if this year's birthday is AFTER calculation date
If DateSerial(yc, mc, dc) >= DateSerial(yc, mb, db) Then
    diff1 = yc - yb
    diff2 = DateDiff("d", DateSerial(yc, mb, db), DateSerial(yc, mc, dc))
    MsgBox "Age is " & Str(diff1) & " years and " & Str(diff2) & " days"
End If

'---if this year's birthday is BEFORE calculation date
If DateSerial(yc, mc, dc) < DateSerial(yc, mb, db) Then
    diff1 = yc - yb - 1
    diff2 = DateDiff("d", DateSerial(yc - 1, mc, dc), DateSerial(yc, mb, db))
    MsgBox "Age is " & Str(diff1) & " years and " & Str(diff2) & " days"
End If

End Sub

Hope this helps

Alan T

Brilliant Alan...works a treat

cheers

Cheers
Tony

This will do it too:

'=========================
Function Age (varBirthDate As Variant) As Integer
       Dim varAge As Variant

      If IsNull(varBirthdate) then Age = 0: Exit Function

      varAge = DateDiff("yyyy", varBirthDate, Now)
      If Date < DateSerial(Year(Now), Month(varBirthDate), _
                           Day(varBirthDate)) Then
         varAge = varAge - 1
      End If
      Age = CInt(varAge)
End Function
'========================================

mmcdonal

BTW (just in passing),

     If IsNull(varBirthdate) Then Exit Function

will work exactly the same as

     If IsNull(varBirthdate) Then Age = 0: Exit Function

Integers default to 0, and the function return value follows that methodology.

For me (might be different for others) the version that just exits is easier to read (and we all have to come back and read our code later...), and I believe it will run about 18 nano-seconds faster.