Calculate Age nearest

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!

J wrote:
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?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

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...

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"J" wrote in message
...
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!

J wrote:
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?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

J wrote:
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(Effe ctiveDate),month(Birthday),Day(Birthday)),year(Eff ectiveDate),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(Effe ctiveDate),month(Birthday),Day(Birthday)),year(Eff ectiveDate),year(EffectiveDate)+1)
- year(Birthday)

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

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.




"Douglas J. Steele" wrote:

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...

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"J" wrote in message
...
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!

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]" wrote:

J wrote:
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(Effe ctiveDate),month(Birthday),Day(Birthday)),year(Eff ectiveDate),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(Effe ctiveDate),month(Birthday),Day(Birthday)),year(Eff ectiveDate),year(EffectiveDate)+1)
- year(Birthday)

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

I see my error. I won't bother correcting it since you are happy with
Doug's solution.
J wrote:
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]" wrote:

J wrote:
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(Effe ctiveDate),month(Birth
day),Day(Birthday)),year(EffectiveDate),year(Effec tiveDate)+1)

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

AgeNearest:

iif(DateValue(EffectiveDate)=DateSerial(year(Effe ctiveDate),month(Birth
day),Day(Birthday)),year(EffectiveDate),year(Effec tiveDate)+1)
- year(Birthday)

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.