Conditional Formatting Dates

[steve12]

Hi my name is Steve and I’ve come across your forum, and that I am grateful for as I am in need of some help with conditional formatting, I have tried for the last few days to get a cell to change colour. Basically I have equipment that requires inspection every six months, so I am trying to get each due date to change i.e. green for 3 months, yellow for 4 months and red for 5-6 months. One of my main problem is the work setup is 2003 and mine is 2010 but to be fair I can’t seem to get either to work.
Can anyone assist with this, help will be greatly appreciated. Ps I have attached the sheet to give you an idea on the layout.

Steve

[Pete_UK]

You have many dates in your example file - which column(s) do you want this to apply to?

Pete

[steve12]

Hi Peter,

All the Due dates as each may have different dates depending when they are done.

Thanks

Steve

[Pete_UK]

I can't remember the menu options for XL2003 (something like Format|Conditional Formatting| then choose Formula Is rather than Cell Value Is), but if you do this in XL2010 and save the file as .xls then it should work for you at work.

First of all, select all the cells that you want this to apply to (eg. G3 to V_whatever), then in XL2010 click on Conditional Formatting | New Rule | Use a Formula ..., then put this formula in the box:

=AND(G3<>"",G3<=DATE(YEAR(TODAY()),MONTH(TODAY())+3,DAY(TODAY())))

or you might need this formula:

=AND(G3<>"",G3>=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY())))

as I'm not sure what you want to identify - I would have thought that you want to closest date to be Red (i.e. not 5 months away).

Anyway, click on Format, then Fill and choose green. OK your way out.

Repeat for your other colours, but change the 3 to 4 and 5 in turn.

Hope this helps.

Pete

P.S. I'm about to go out now, so I won't be able to get back to you (if it is necessary) until tomorrow evening.