if date = x, then age = y

[goldcomac]

I've been at it for hours and I'm at the point where I'm just really
messed up.

I'm trying to write a formula that uses dates (mm/dd/yy) to figure out
what age group the kids fall in to. The group the kids fall into is
determined by their age as of May 15th. In other words, if she is 11 on
the 14th, she competes in the 11-12 age group. If she's 11 on the 15th,
she competes in the 11-12 age group, but if she's 11 on the 16th, she
competes in the 9-10 age group. We've got 8&Under, 9-10, 11-12, 13-14,
15-16, and 17-18.

I think I have to first figure out their actual age in one column and
then do another formula to take it to the end result I want, but I'm no
longer sure.

If anyone can help, I would appreciate it.

Thank you.

 

[daddylonglegs]

If your date of birth is in A2 you can use this formula to give the
correct category. Note: it will give different results on 1st Jan 2007

=LOOKUP(DATEDIF(A2,DATE(YEAR(TODAY()),5,15),"y"),{0,9,11,13,15,17,19},{"8&under","9-10","11-12","13-14","15-16","17-18","too
old"})

 

[goldcomac]

If your date of birth is in A2 you can use this formula to give the
correct category. Note: it will give different results on 1st Jan 2007

=LOOKUP(DATEDIF(A2,DATE(YEAR(TODAY()),5,15),"y"),{0,9,11,13,15,17,19},{"8&under","9-10","11-12","13-14","15-16","17-18","too
old"})

Thank you very much Daddy. It worked like a charm!