Calculate School Grade Based On Age

[mcgonma]

I want to create a formula that will calculate what grade in school a group of kids should be based on their birthday.
For example, Jim born on 02/15/08 would be 4 3/4 and be in Pre-K (ages 2 to 5). Sam born on 10/10/07 would be about 5 1/4 and be in Kindergarten. Bob born on 10/10/06 would be about 6 1/4 and be in 1st Grade.
I have a roster of 60 kids that I want to figure out what grade they should be in based on their birthday.

 

[AlphaFrog]

I want to create a formula that will calculate what grade in school a group of kids should be based on their birthday.
For example, Jim born on 02/15/08 would be 4 3/4 and be in Pre-K (ages 2 to 5). Sam born on 10/10/07 would be about 5 1/4 and be in Kindergarten. Bob born on 10/10/06 would be about 6 1/4 and be in 1st Grade.
I have a roster of 60 kids that I want to figure out what grade they should be in based on their birthday.

Don't you need to calculate their ages for the start of the school year or some specific cut-off date? It doesn't matter that Sam is 5.25 as of today. It matters what his age is at the start of the school year calendar or am I missing something? If correct, what is your cut-off date to determine their age to begin a school grade?

 

[mcgonma]

I'm setting the start date of the school year at 9/1/2012. A Pre-K student is up to 5 years old, 1st grade is 5-6 y/o, 2nd grade is grade is 6-7 y/o, 3rd grade is 7-8 y/o, etc.

 

[AlphaFrog]

Try something like this where A1 is the DOB.

=LOOKUP((DATEVALUE("9/1/2012")-A1)/365.25,{0,2,5,6,7,8,9},{"Too young","Pre-K","K","1st","2nd","3rd","4th"})

 

[easy2understandexcel]

To keep the formula simple, I would setup a basic table with the dates and grade and use a vlookup statement. Example here: Excel 2007 Tutorial VLOOKUP (Part 2: "True" Lookup Range explained) - YouTube

 

[mavaddat]

Try something like this where A1 is the DOB.

=LOOKUP((DATEVALUE("9/1/2012")-A1)/365.25,{0,2,5,6,7,8,9},{"Too young","Pre-K","K","1st","2nd","3rd","4th"})

I like this solution and I have slightly modified it for universality (beyond the year 2012) as follows:

=LOOKUP((NOW()-[@Birthday])/365.25,{0,2,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19},{"Too young","Pre-K","K",1,2,3,4,5,6,7,8,9,10,11,12,13})