skip navigation

Age Formula

Excel formula to calculate age group:

 

=IF(ISBLANK(ColRow),"",MAX(IF(MOD(IF(MONTH(ColRow)>8,0,1)+ (YEAR(NOW())-IF(MONTH(NOW())<9,2,1)-YEAR(ColRow)),2)>0.5,1,0) +IF(MONTH(ColRow)>8,0,1)+(YEAR(NOW())-IF(MONTH(NOW()) <9,2,1)-YEAR(ColRow)),6))

 

This formula can be used to calculate the age group for the current year.  Enter the formula into an Excel cell, then change the variable "ColRow" to the cell reference where the date of birth is entered.

For example, copy the formula to cell A1, then enter the date of birth in cell B1.  Replace all instances of "ColRow" in the formula with the reference "B1," so the formula reads as follows:

=IF(ISBLANK(B1),"",MAX(IF(MOD(IF(MONTH(B1)>8,0,1)+(YEAR(NOW())-IF(MONTH(NOW())<9,2,1)-YEAR(B1)),2)>0.5,1,0)+IF(MONTH(B1)>8,0,1)+(YEAR(NOW())-IF(MONTH(NOW())<9,2,1)-YEAR(B1)),6))