inhibit rounding

  • Thread starter Thread starter Evelyn Ballantyne
  • Start date Start date
E

Evelyn Ballantyne

In a birthday list I am calculating from the date of
birth to get the result "how old next birthday"

The date of birth is in the field called bday.
I am using this formulae on the form.

=(Now()-[bday])/365.25+1
How do I inhibit the rounding because it rounds up and in
some cases shows the wrong age.
Many thanks in anticipation
 
Hi Evelyn

The method you are using to calculate age is not completely reliable. Here
is a function that will work every time:

Public Function AgeInYears( _
dtBirthDate As Variant, _
Optional dtAgeAt) As Variant
If IsDate(dtBirthDate) Then
If IsMissing(dtAgeAt) Then dtAgeAt = Date
AgeInYears = DateDiff("yyyy", dtBirthDate, dtAgeAt) _
+ (dtAgeAt < DateSerial(Year(dtAgeAt), _
Month(dtBirthDate), Day(dtBirthDate)))
End If
End Function

Paste the code above into a standard module, and you can then use the
formula:
=AgeInYears([bday])

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.
 
Thank you so much Graham, I will try it now. - Evelyn
-----Original Message-----
Hi Evelyn

The method you are using to calculate age is not completely reliable. Here
is a function that will work every time:

Public Function AgeInYears( _
dtBirthDate As Variant, _
Optional dtAgeAt) As Variant
If IsDate(dtBirthDate) Then
If IsMissing(dtAgeAt) Then dtAgeAt = Date
AgeInYears = DateDiff("yyyy", dtBirthDate, dtAgeAt) _
+ (dtAgeAt < DateSerial(Year(dtAgeAt), _
Month(dtBirthDate), Day(dtBirthDate)))
End If
End Function

Paste the code above into a standard module, and you can then use the
formula:
=AgeInYears([bday])

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

In a birthday list I am calculating from the date of
birth to get the result "how old next birthday"

The date of birth is in the field called bday.
I am using this formulae on the form.

=(Now()-[bday])/365.25+1
How do I inhibit the rounding because it rounds up and in
some cases shows the wrong age.
Many thanks in anticipation
Click to expand...


.
Click to expand...
 
Back
Top