Excel Formatting with dates

[Malcolm Rochester]

Hell, please be gentle I am fairly new to Excel!
I am trying to get cells in a worksheet to turn differnt colours depending on date criteria as follows:
If a date occurs today or before the cells to turn red.
If a date is tomorrow plus 30 days the cells to turn yellow.
If a dare is more than 30 days old the cells to turn green.
Now for the hard part! If there is no date in the cell no colouring to be in the cell.
I get the feeling that this should be fairly easy to do but I can't quite get all the things working together.
Can you help?
Thanks in advance. Geordie

 

[Becky]

Welcome to the forum!

Have you been using conditional formatting? If so what rules have you tried to put in place?

 

[Malcolm Rochester]

Hello Becky,

I was using the following conditional formatting:
Format only cells that contain / Cell value / Less than / =NOW()+30 / Format yellow
Format only cells that contain / Cell value / Less than / =NOW() / Format red
Format only cells that contain / Cell value / Greater than / =NOW()+30 / Format green
Then I tried: Use a formula to determine which cells to format / =ISBLANK(cell range)=True / No format set
Cheers.

 

[Becky]

Format only cells that contain / Cell value / Less than / =NOW()+30 / Format yellow
Format only cells that contain / Cell value / Less than / =NOW() / Format red
Format only cells that contain / Cell value / Greater than / =NOW()+30 / Format green

That should work... If you go to 'Manage rules' and then edit the rules do any of them have quotes around the formula? Might look like this: ="=NOW()+30"

If so then deleting the quote marks and extra equals sign should fix it. I've had this happen to me, it's an Excel bug.