Excel calculation - age at nearest birthday 2

[SBuzzT]

I need to build a calculator similar to what insurance companies use.

What I need to be able to do is, if the current date is 6 months or less from your last birthday, to display that age and date.

If current date is 6 months + 1 day (or more) from your last birthday, to display your next birthdate and age on that date. I have tried various things, and it does not seem like it should be that complicated, but I can't seem to figure it out.

Any ideas?

 

[SkipVought]

Hi,

I have tried various things...

What have you tried?

Have you looked at the DATE formula?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

=DATEDIF(A2,TODAY(),"Y")+IF(DATEDIF(A2,TODAY(),"yd")>365/2,1) is A2 contains birthdate.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[SkipVought]

xlhelp,

See! You ARE a GIANT!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SBuzzT]

Thanks xlhelp, but I believe there must be a margin of errror with that formula. If I use the birthdate 15 july 1969 (this should be 6 months and 0 days from today), I get the age at the next birthday instead of previous as I should if 6 months or less.

It's still better than anything I have, but I need to make it more accurate.

 

[SkipVought]

SBuzzT,

Did you try to take the ball and RUN with it, meaning TWEEK it a bit??? Try to make it work???

Furthermore, you've been posting on Tek-Tips for over 4 years, yet NEVER ONCE have you...

[blue]
Thank Tek-Tip Contributor
for this valuable post![/blue].

The [purple]little purple Stars[/purple] accomplish several important things.

First, it gives positive feedback to contributors, that their posts have been helpful.

Second, it identifies threads as containing helpful posts, so that other members can benefit.

And third, it identifies the original poster (that's YOU, BTW), as a grateful member, that not only receives, but is willing to give tokens of thanks.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

SBuzzT

Here's what you are looking for.

=DATEDIF(A2,TODAY(),"Y")+IF(DATEDIF(A2,TODAY(),"ym")=6,IF(DATEDIF(A2,TODAY(),"md")=0,0,IF(DATEDIF(A2,TODAY(),"yd")>=365/2,1)))

Thank you for the star; I would rather earn the reward

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[SBuzzT]

Thanks again xlhelp - another star

Here is something I came up with which seems to work pretty well too. (Wouldn't have come up with it without your idea though). A3 would contain the birthdate.

=IF(AND(DATEDIF(A3,today(),"ym")>=6, DATEDIF(A3,today(),"md")>0),DATEDIF(A3,today(),"y")+1,DATEDIF(A3,today(),"y"))

Hey, do you think I'll get a star? (Kidding)

 

[strongm]

It might be worth pointing out that, since TRUE in Excel has a value of 1

+IF(DATEDIF(A2,TODAY(),"yd")>365/2,1)

could be written as the fractionally calculationally less intensive

+(DATEDIF(A2,TODAY(),"yd")>365/2)