Adding a "between" formula in conditional formatting

Hey I've tried looking around here to solve this problem, but didn't find a solution. Basically I'm creating an excel spreadsheet covering names and dates of people who have taken online training courses that have to be retaken every year. I want the spreadsheet to change the colors of the cells based on how close the person's training qualification is about to expire. So far this is all I've come up with in conditional formatting...

If someone conducted training more than 11 1/2 months ago then the cell will turn red
=ROUND(NOW()-F1,0)>350

If someone If someone conducted training less than 11 months ago then the cell will turn green
=ROUND(NOW()-F1,0)<335

For some reason I can't get excel to accept a formula that will change the cell color to yellow if the person is in between 335 and 350 days.

Thanks

Instead of NOW(), you can use TODAY(), then it just uses the date, instead of date & time, but for the yellow highlight that you want, try this:

Formula:

Please Login or Register  to view this content.

- Moo

Okay the final result was by going into "Conditional Formatting" > "Edit Rule" > "Use a formula to determine..." and adding these formulas into the text box and formatting the fill to the appropriate color.

For the red cells,

Please Login or Register  to view this content.

Yellow,

Please Login or Register  to view this content.

And green cells.

Please Login or Register  to view this content.

Thanks to Moo the Dog for the fast response.

You're welcome, Reizor. One thing I'll mention though is that you changed the yellow highlight code from '>=335 or <=350' to '>335 or <350'. The problem with that is that when F1 is exactly 335 or 350 days prior to today's date, conditional formatting won't apply, since they aren't in any of the ranges in your requirements.

- Moo

- - - - - - - - - -
If you are satisfied with the solution(s) provided, please mark the thread as [SOLVED] by clicking on the words 'Thread Tools' above your 1st post in this thread. Then select 'Mark Thread as Solved'.

Also, a good way to 'Say thanks' to those who have helped is to click on the star below their name in one of their posts. It's always appreciated.