Conditional formating

U

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.
 
B

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
 
U

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
 
B

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)
 
U

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.
 
M

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
 
B

Bob Phillips

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


--
HTH

Bob Phillips

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

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.
 
Top