How do I lock the date when I enter data to highlight a cell dependent on that date?

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

One way would be to enter the "date at age of test" (as data, not a formula) next to the result. Use this for the conditional formatting of the result. Seems quite straightforward, so I must have missed something?

Regards
Alastair

The Excel attachment is our district's entire document. The other attachment are the standards for each fitness test (use the lowest value for each age). Again, if the student achieves the standard for any of the fitness tests, we want that cell highlighted. As they continue through school, we want to be able to look back at their previous tests to see if they've achieved the standard in the past.

Phew! So many opportunities to really get this wrong!

I have put down a few thoughts on the attached sheet that you may care to develop.

However, I believe that the basic concept of the results page is (how do I put this so as not to cause offence (or even offense)) is wrong.

What I like to see on this sort of thing is where a certain figure is average. Thus by scanning the page I can see who is good (or not). Your results for (say) Pacer is not immediately obvious, as a score of 43 could be below standard for an 18 year old boy but fantastic for any age girl.

My solution would be to have a sheet that marks the performance in relation to the standard so that those who achieved the standard get a mark of 0 with others getting plus or minus on a reasonable basis (OK so you could fudge it so that everyone get a positive score, but you see what I mean).

Sorry if I have strayed too far from the Excel problem. If you think my ideas are completely ridiculous, please tell me (I'm pretty thick skinned! ) otherwise I am happy to work with you towards a conclusion.

Regards
Alastair