Conditional formatting for expiry dates (excel 2007)

Hello

I have searched this forum and used previous answers to assist with conditional formatting for expiry dates.

They don't seem to fix my problem though..

I have a column - it includes dates in some rows but not in all. I want to mark between tomorrow and 30 days with yellow, today and older with red.

I have used...

cell value between =TODAY()+1 and =TODAY()+30 (format yellow)

cell value equal to =TODAY() (format red, font white)

cell value less than =TODAY() (format red, font white)

formula =ISBLANK(G1)=TRUE (format white)

When I run this on a new sheet with "test" data it seems to work. But when I enter it on an existing worksheet it makes empty cells red, changes font in some cells to white but does not change the background colour and does not mark the cells which should now be red and yellow. I have the cells set as "short date" too (don't know if this makes a difference?).

What am I doing wrong? Any help would be appreciated!

Thanks!
Lisa

Hi Lisa,

Please see attached for a possible solution. Hope it helps.

conditional format solution.xls