Condirional Fomatting Using Dates in a cell

[freckles81]

Hi

can someone please help me out, I am trying to create a formula that will have the row turn orange when the data in column E has exceeded 9 weeks, red when its exceeded 11 weeks but I am at a major loss here (been doing a huge sheet for hours now and the simplest thing has stopped me in my tracks.)
I have posted this against a similar thread but I was hoping to increase my chances of a reply by starting a new thread

I tried :

=IF(AND((E2-TODAY())>63, (E2-TODAY())<70),TRUE,FALSE)
that should go orange
=IF((E2-TODAY())>69,TRUE,FALSE)
that should go red

but it doesn't work, I want it to look at the data in that cell and if the date you are currently at has exceeded 11 weeks it changes.

Please help a very frustrated young lady as I am about to cry!!

regards,
Freckles81

 

[villy]

In conditinal Formatting:

try this formula,
=AND((E2-TODAY())>63, (E2-TODAY())<70) ' This should turn orange
=(E2-TODAY())>69 ' This should turn red

 

[Andrew Poulsom]

Shouldn't it be TODAY()-E2?

 

[freckles81]

Hello to you both,

fab it worked, the formula Villy gave me was spot on and i changed it to =(TODAY()-E2)>69 as Andrew suggested.

You have put a stop to a lot of frustration for me today

regards,
Freckles81