Age formula for Excel - 11/22/01 02:13 AM
Are you using a spreadsheet to keep your club's roster, and wish you had a formula that would easily calculate each wrestler's current age group (using the Kids Division's August 31 cutoff)? The following is a formula I've used for years in Microsoft Excel; it calculates the age group for the current year, based on a date of birth entered in a separate cell (in the example, the date of birth would be entered in cell "F16"). If anyone has a better method of doing this, please post your solution.
Here's the formula:
=IF(ISBLANK(F16),"",MAX(IF(MOD(IF(MONTH(F16)>8,0,1)+(YEAR(NOW())-IF(MONTH(NOW())<9,2,1)-YEAR(F16)),2)>0.5,1,0)+IF(MONTH(F16)>8,0,1)+(YEAR(NOW())-IF(MONTH(NOW())<9,2, 1)-YEAR(F16)),6))
Yeah, I know it's kind of long, but all you have to do is type it in once, then copy it to all the other wrestlers.
Here's the formula:
=IF(ISBLANK(F16),"",MAX(IF(MOD(IF(MONTH(F16)>8,0,1)+(YEAR(NOW())-IF(MONTH(NOW())<9,2,1)-YEAR(F16)),2)>0.5,1,0)+IF(MONTH(F16)>8,0,1)+(YEAR(NOW())-IF(MONTH(NOW())<9,2, 1)-YEAR(F16)),6))
Yeah, I know it's kind of long, but all you have to do is type it in once, then copy it to all the other wrestlers.