Working Age Group Signed From DOB and Date Signed

[Bellio3105]

Hi all,

Having a bit of trouble working a formula which can show which age group a player joined a sports club from his date of birth and date registered.

I've attached a sample of the spreadsheet.

The age group is based on his age on 31st August of Each Year.

So a player aged 7 on 31st August 2012 is U7
A player aged 8 on same date is U8.

I have worked this out previously using-

="U"&DATEDIF(H63,DATE(2012,8,31),"y")

But this only allows for 1 date to be input and if I alter (2012,8,31) to represent date signed, answers can be incorrect due to movement away from 31st August.

Any help would be much appreciated.

Thanks,

Dan

[Ace_XL]

Use..

="U"&DATEDIF(A2,DATE(YEAR(B2)-IF(MONTH(B2)>8,1,0),8,31),"y")

copy down

[Bellio3105]

Works perfect.

Thanks,