Conditional formating

[Unplugged]

Need help figuring out a formula please.

If "date entered" in cell exceedes todays date by 4 days, then format
the cell background "Red".

Thanks so much for any help you can give.

 

[Bob Phillips]

Use a formula of

=A2>=TODYA()+4

where A2 is assumed as the first cell with CF

 

[Bernard Liengme]

Click on the cell (let's call it A1)
Use Format | Conditional Formatting
Formula: $A$1-TODAY()>4
Then set colour as needed
best wishes

 

[Bob Phillips]

Should be TODAY() of course

Use a formula of

=A2>=TODYA()+4

where A2 is assumed as the first cell with CF

 

[Unplugged]

Wow! Thanks so much for the quick reply.
Thought your formula works for the question I asked, I'm afraid I
incorrectly asked my question.
My apologies. I will try again.

In Cell A1 I have entered a date. Eg. 01/01/06
What I want is for that cell to turn red when 4 days have passed the
entered date.

Looking forward to your reply.
Cheers

 

[Bob Phillips]

Exactly 4 days?

=A2=TODAY()-4

or more than 4 days?

=A2<=TODAY()-4

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Unplugged]

OK
I got it to work by using
=TODAY()>A1+4
The cell now turns 'red' when 4 days have passed, which is exactly what
I wanted.

Now the only problem is...
When I copy that conditional formating to another cell, which is
'empty', it gets filled in with 'red'.
Is there a way to stop this from happening?

Thanks again.

 

[Michael Behm]

You could use an "IF" statement in your conditional formula:

=IF(COUNT(A1)<>0,TODAY()>A1+4,0)

Then if there's something in the cell, it checks the date, otherwise, it
assumes you don't want to conditionally format it.

Michael

 

[Bob Phillips]

=AND(A1<>"",TODAY()>A1+4)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Unplugged]

Thanks so much for all your help people.
It now works perfectly!

Really nice to know there are people out there who are so generous with
their knowledge and time.

Best wishes, and thanks again.