win1forgipp
New Member
- Joined
- Apr 27, 2015
- Messages
- 7
This sheet monitors individual's physical fitness scores. If members score <90 they must retest every 6 months with a diagnostic performed two months prior (4 months mark). If they score 90 or higher they will retest yearly with a diagnostic performed at 10 months.
I have several rows of dates, corresponding to when an individual took the test. In the second column is the score the individual received. The third column contains this formula:
=IF(B4>89.9,(A4+300),(A4+120))
This formula is adding 300 days to the next time the individual tests vice 120 if they scored under a 90%. (I'm sure there is a better way than 300 because thats not accurate)
A4 represents the date they took the test.
The third column is conditionally formatted with three rules. IF date is THIS month, it turns green, LAST month it turns red and NEXT month it turns yellow.
My question resides with my 4th column...In this column will be the date they performed the diagnostic to judge progress before actual test.
When I place a date in that field, I'd like the column that is conditionally formatting based on dates to stop formatting color and remain white basically showing on a spreadsheet that they were once overdue (red) but have since taken the diagnostic which puts them back in the white.
Here is the file on my Drive account.
https://drive.google.com/file/d/0By3TusOiWiVUb0V6cE5kbVF1T0k/view?usp=sharing
Thanks for helping me out.
cheers,
Gipp
I have several rows of dates, corresponding to when an individual took the test. In the second column is the score the individual received. The third column contains this formula:
=IF(B4>89.9,(A4+300),(A4+120))
This formula is adding 300 days to the next time the individual tests vice 120 if they scored under a 90%. (I'm sure there is a better way than 300 because thats not accurate)
A4 represents the date they took the test.
The third column is conditionally formatted with three rules. IF date is THIS month, it turns green, LAST month it turns red and NEXT month it turns yellow.
My question resides with my 4th column...In this column will be the date they performed the diagnostic to judge progress before actual test.
When I place a date in that field, I'd like the column that is conditionally formatting based on dates to stop formatting color and remain white basically showing on a spreadsheet that they were once overdue (red) but have since taken the diagnostic which puts them back in the white.
Here is the file on my Drive account.
https://drive.google.com/file/d/0By3TusOiWiVUb0V6cE5kbVF1T0k/view?usp=sharing
Thanks for helping me out.
cheers,
Gipp
