I teach Physical Education and each year students perform fitness tests and their scores are recorded in an Excel spreadsheet that is shared with the other PE teachers in grades 6-12. As teachers, we want to see which students have met the standards for a particular fitness test. In the past, teachers would enter each student's score and put an asterisk by it if they met the standard for that test. These standards change based on the age of a student, so the teacher always has to check how old each student is and cross-reference that with the standards while entering in the data. To expedite the process, I was able to create a "Birthdate" column and an "Age" column. The age column is the result of a simple formula using the "Birthdate" column and the Today() function. From than, I was able to create rules for the cells so that if a student achieved the standard for the test, the cell would be highlighted. The only problem is when a kid's age changes, it affects the rules, therefore affecting the highlighted cell. Here's an example; John is a male and his birthday is 5/1/98. Right now he is 14 years-old. In his push-up test, he will reach the standard if he can do 14 push-ups. We conducted the test today (4/29/13) and he did 15 push-ups. In my current spreadsheet, the cell will be highlighted because it completes the rule. However, on 5/1/13, he will be 15 years-old, which doesn't complete the rule for that cell, and causing the cell to no longer be highlighted. Is there a way to lock the date of data entered and put that in a formula or rule to highlight a cell? Our goal is to be able to look up all of a student's fitness tests so we can see how many times they achieved the standard for a particular test while in middle school and high school.

I've done some research but I haven't been able to find anything that specifically can handle this request.

Please advise,

Ben