calculate a person's age based on the age they were on a specific date of each year

[flintcs]

Basically, I'm trying to find a person's age as of any date based on the age they were on April first for insurance purposes.

So if looking at a person on 9/18/13 (today()) who was born on 5/1/83, they would be counted as the age of 29 NOT 30 because as of 04/1/13 they were still 29. Seems easy enough to write into a formula so I can record it into a macro but because I am not focusing on the year when I go to adjust their age it is not recognizing my MMDD as a date.

First I calculated their date as of today. (Today()-(DOBcell)/365.25

Then I wanted to say if they were born between the months of 04/01 and today subtract 1[/B]. This is where I am simply trying to say =if (and(the DOB>April 1, DOB<=Today()), Age-1, age)

The part that doesn't work is trying to find out if their birth month and date is less than or equal to today.

=IF(AND(MONTH(AB2)> MONTH(AA$1),MONTH(AB2)>=MONTH(AD$1),Z2-1,Z2)

Any ideas...I've tried everything but Excel is not able to tell me which is greater when only looking at a month and day and not a year. I may need to rethink this whole thing.

Thank you in advance!!!!!

[Tony Valko]

See this...

http://www.cpearson.com/Excel/datedif.aspx

[DBY]

Hello
I was working on this formula when Tony replied. It uses the DATEDIF function as he suggests. Assuming you are trying to work out the age from the DOB to the 1st of April in the current year, does the following formula work?

Please Login or Register  to view this content.

DOB is the cell with the date of birth.

DBY

[flintcs]

Thank you!

[flintcs]

Thank you. I had thought of datedif but I didn't know there was a way to pull the current year and set a month and date. This was so helpful and I am making notes of all of this.

[Tony Valko]

You're welcome!

[thorrrr]

OK Guys next query

I have DOB in cell A34 19/11/1959 with formula =DATEDIF(A34,TODAY(),"y")&" years "&DATEDIF(A34,TODAY(),"ym")&" months "&DATEDIF(A34,TODAY(),"md")&" days" giving me 53 yrs 10 months 3 days
below it i have in cell A35 22/02/1974 with formula =DATEDIF(A35,TODAY(),"y")&" years "&DATEDIF(A35,TODAY(),"ym")&" months "&DATEDIF(A35,TODAY(),"md")&" days" giving me 39 yrs 7 months days

Question what formula / or how do i subtract the later to work out the difference between the two dod to show me in Years Months and Days

[thorrrr]

PLEASE IGNORE DONE IT

OK Guys next query

I have DOB in cell A34 19/11/1959 with formula =DATEDIF(A34,TODAY(),"y")&" years "&DATEDIF(A34,TODAY(),"ym")&" months "&DATEDIF(A34,TODAY(),"md")&" days" giving me 53 yrs 10 months 3 days
below it i have in cell A35 22/02/1974 with formula =DATEDIF(A35,TODAY(),"y")&" years "&DATEDIF(A35,TODAY(),"ym")&" months "&DATEDIF(A35,TODAY(),"md")&" days" giving me 39 yrs 7 months days

Question what formula / or how do i subtract the later to work out the difference between the two dod to show me in Years Months and Days

[FDibbins]

thorrr...
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.