The following commonly-used function fails on a small percentage of dates; the specific failure dates depend on the birth date and current date.
Function Age(Birthdate as Date) as Integer
Age = Datediff("d", Birthdate, Date) / 365.25
End Function
Instead try this:
Function Age (Birthdate as Date) as Integer
Age = DateDiff("yyyy", Birthdate, Date) + _
(Date < DateSerial(Year(Date), Month(Birthdate), Day(Birthdate)))
End Function
This takes advantage of the fact that True has a value of -1 and False has a value of 0... If this expression is True (the birthdate in the current year hasn't happened yet), you end up subtracting 1 day.
If you want to enter your own end date instead of using today's date, here is the modification:
Function Age(Birthdate As Date, Enddate as Date) As Integer
Age = DateDiff("yyyy", Birthdate, Enddate) + _
(Enddate < DateSerial (Year(Enddate), Month(Birthdate), Day(Birthdate)))
End Function
- Mostly from an article by Ken Getz in MS Office & VB Developer Mag, p.64
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.