How do I calculate someones age on Jan 1 of the current year?

[Guest]

I have the date of birth and can figure out how old they are today, but I
need to know how old they were on January 1st of the current year. The
object is to calculate it automatically every year without a manual input of
a date.

Anyone done this?

 

[Guest]

Here is a function that will do that for you. I get suspisious when you say
you want to do it once per year. Why? If you are storing this value in a
table, you should not. No calculated data should be stored in tables. Only
calculate it when you need to present it to the user.

Function CalcAge(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if
missing.
'Return: Whole number of years.
Dim dtmDOB As Date
Dim dtmAsOf As Date
Dim dtmBDay As Date 'Birthday in the year of calculation.

CalcAge = Null 'Initialize to Null

'Validate parameters
If IsDate(varDOB) Then
dtmDOB = varDOB

If IsDate(varAsOf) Then 'Date to calculate age from.
dtmAsOf = varAsOf
Else
dtmAsOf = date
End If

If dtmAsOf >= dtmDOB Then 'Calculate only if it's after person was
born.
dtmBDay = DateSerial(year(dtmAsOf), Month(dtmDOB), Day(dtmDOB))
CalcAge = DateDiff("yyyy", dtmDOB, dtmAsOf) + (dtmBDay > dtmAsOf)
Else: CalcAge = Null 'Return Null if AsOf is before birthday
End If
End If
End Function

 

[Guest]

The calculation is for a rate change that happens every Jan 1st and is based
on the persons age on Jan 1st. I need to know how to calculate the age of a
person on Jan 1. That age will determine the monthly fee for the current
year. Next Jan 1 the rate will change according to what the age is on Jan 1.
I am not storing the date in a table. Thoughts?

 

[Douglas J. Steele]

What's wrong with the function Klatuu gave you? It'll do exactly what you're
asking for. Pass DateSerial(Year(Date), 1, 1) as the 2nd argument.

 

[Guest]

Then you are doing it correctly. As Douglas Steele said, pass the person's
birthday as the first argument and DateSerial(Year(),1,1) as the second
argument. That will return the person's age as of January 1 of the current
year.

 

[Guest]

Thank you I will try that.
--
Dar

Then you are doing it correctly. As Douglas Steele said, pass the person's
birthday as the first argument and DateSerial(Year(),1,1) as the second
argument. That will return the person's age as of January 1 of the current
year.