Calculate Age nearest

[J]

Any ideas on a formula to calculate someone's age on their closest birthday
(last or next)?

For example:
Birthday = 1/7/1943
Effective date = 8/1/08
Age nearest (as of effective date) = 66

Another example: Let’s say I am 21 with a DOB of 7/15. If a policy is
issued on 1/1 then my insurance age is 21 (my last birthday is nearer than my
next). If the policy is issued on 2/1 then my insurance age is 22 (my next
birthday is nearer than my last).

Thanks!

 

[Bob Barrows [MVP]]

Any ideas on a formula to calculate someone's age on their closest
birthday (last or next)?

For example:
Birthday = 1/7/1943
Effective date = 8/1/08
Age nearest (as of effective date) = 66

Another example: Let's say I am 21 with a DOB of 7/15. If a policy is
issued on 1/1 then my insurance age is 21 (my last birthday is nearer
than my next). If the policy is issued on 2/1 then my insurance age
is 22 (my next birthday is nearer than my last).

What database? And what datatype is the field used to store the DOB?

 

[Douglas J. Steele]

You could use DateDiff to determine the difference between the DOB and
current date in months, then look at the remainder (use Mod 12). If it's
less than 6, ignore it. If it's greater than 6, round up. Your call if it's
equal to 6...

 

[Bob Barrows [MVP]]

Any ideas on a formula to calculate someone's age on their closest
birthday (last or next)?

For example:
Birthday = 1/7/1943
Effective date = 8/1/08
Age nearest (as of effective date) = 66

Another example: Let's say I am 21 with a DOB of 7/15. If a policy is
issued on 1/1 then my insurance age is 21 (my last birthday is nearer
than my next). If the policy is issued on 2/1 then my insurance age
is 22 (my next birthday is nearer than my last).

Oh duh! This is the Access group! I thought I was in the ADO group! Let's
assume you are storing the birthday and effective dates in Date/Time fields
(recommended).

I have to go to work now. If no one has given you the answer by the time I
check this group again, I will throw something together. From my initial
thoughts, it appears possible to do this in a query, but it would be a
complicated mess of iif() functions. If I was intending to do this for
myself, I would be leaning toward doing it in a VBA function. Which
technique would you prefer?

 

[Bob Barrows [MVP]]

Any ideas on a formula to calculate someone's age on their closest
birthday (last or next)?

For example:
Birthday = 1/7/1943
Effective date = 8/1/08
Age nearest (as of effective date) = 66

Another example: Let's say I am 21 with a DOB of 7/15. If a policy is
issued on 1/1 then my insurance age is 21 (my last birthday is nearer
than my next). If the policy is issued on 2/1 then my insurance age
is 22 (my next birthday is nearer than my last).

Actually, it is not as bad as I originally was thinking. All that is needed
is the year of either the closer of the prior or next birthday:

iif(DateValue(EffectiveDate)>=DateSerial(year(EffectiveDate),month(Birthday),Day(Birthday)),year(EffectiveDate),year(EffectiveDate)+1)

Now it's a simple matter to subtract the year of the DOB from that
calculated year:

AgeNearest:
iif(DateValue(EffectiveDate)>=DateSerial(year(EffectiveDate),month(Birthday),Day(Birthday)),year(EffectiveDate),year(EffectiveDate)+1)
- year(Birthday)

 

[J]

Thanks Doug.

This sort of worked.

The only thing I had to adjust for was if the birthday is the same month as
the effective date (IE birthday is 8/2 and effective date is 8/1). The
remainder would be 0, but when using "age nearest" logic, these people are
considered a year older. In solving that issue, it created the issue of if
the birthday is the same day as the effective date (remainder would still be
0, but "age on effective date" equals "age nearest"). I just had to build
those exceptions into the if statement.

It all worked out in the end. Thanks again.

 

[J]

Hi Bob -

This didn't quite work right. If you plug in the dates I used in my
example, it calculates the age nearest as 65 (should be 66). It also seems
to be adding two years to everyone's age for those with birthdays in aug-dec.

Based on Doug's suggestion, I used the datediff and mod 12 to get what I
needed.

Thanks for trying.

 

[Bob Barrows [MVP]]

I see my error. I won't bother correcting it since you are happy with
Doug's solution.