If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Calculate Age nearest
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" |
#3
|
|||
|
|||
Calculate Age nearest
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! |
#4
|
|||
|
|||
Calculate Age nearest
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" |
#5
|
|||
|
|||
Calculate Age nearest
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" |
#6
|
|||
|
|||
Calculate Age nearest
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! |
#7
|
|||
|
|||
Calculate Age nearest
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" |
#8
|
|||
|
|||
Calculate Age nearest
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. |
Thread Tools | |
Display Modes | |
|
|