Conditional formatting (i think) assistance

Hello,

The idea of this portion of my spreadsheet is to visually see current, overdue, and upcoming orders based off the date listed in Column B, Forecasted Date.

I would like the factored value (Column E) to automatically populate the appropriate month based off of the date entered in Column B. I would also like the month cell to change color depending on its relation to the current date. I.e.:

• If the date is more than 2 months away, the cell will be green
• If the date is within 2 months, the cell will be yellow
• If the date is equal to today or late, the cell will be red.

I have been able to either populate the cell based off the date or change the color based off of the date. However, I am having problems combining both of these together.

Any assistance would be greatly appreciated.

Thank you!

Welcome to Excel Forum!

Sorry, I'm a Nats fan

Try the attached.

Although conditional formatting is much more powerful in 2007, I still haven't mastered how to deal with absolute vs. relative formatting in it, and how to control what cells the formats apply to. I thought it was easier in 2003. I feel like the solution I gave is unnecessarily unwieldy, since I used INDIRECT and ROW rather than simple relative references, but I couldn't get simple relative references to work.