Conditional Formatting

[RBastin]

Hi, I'm new to this and am looking for some help. I have excel 2003 and would like to know, if i can and if so how i can do the following:

I have 3 cells the first will always have a date in ((F3) Due Date) the second may have a date ((G3) Extended Due Date) and the third may also have a date ((J3) Date Filed).

What i want is for F3 to turn red, 3 days before the date, if no date entered in cells G3 and J3.

I also want G3 to turn red, three days before date, if no date entered in J3.

Please can someone help me i have spent hours on this and got no where.

Thank you
Ruth

 

[villy]

From Format > Conditional Formatting:
For F3:
Condition1: Change "Cell Value Is" to "Formula Is" then in formula bar type: =AND($G$3="",$J$3="")
Condition2: "Cell Value Is" then "equal to" : =F3-3 (you can change to "between" : =F3-3 and =F3 stays red 3days,2days,and a day before its date)
in both condition click Format>Font>Color>Red

For G3:
Condition1: "Formul Is" : =$J$3=""
Condition2: "Cell Value Is" then "equal to" : =G3-3 (you can change to "between" : =G3-3 and =G3)
in both condition click Format>Font>Color>Red

Hope it helps...

 

[asking]

I posted, but the less than symbols did not work although it worked in the preview

 

[RBastin]

Hi Villy,

I tried your formula but it all stays red, tried playing with it got nowhere. I'm thinking i need to some how put today's date in the formular some how as it does not know that F3 or G3 are three days before the date. Does that make sense? Does anyone else have any ideas.

Thank you

 

[asking]

Try this, i posted before but can't get the less than symbol to work. so where is says less than insert the sybol.

Select Cell G3, then in conditional formatting enter these 2 rules.

Rule 1 which must be first in the list ~
=ISNUMBER(J3) tick the box stop if true (don't apply any format), click apply

Rule 2
=DAY(F3)-4lessthanDAY(TODAY()) apply your red format and click apply.

Now select cell J3 and repeat the same formulas.

Important that rule 1 is the first rule in the list. Where rule 2 is lessthan replace with the less than symbol.

Cheers

 

[villy]

From Format > Conditional Formatting:
For F3:
Condition1: Change "Cell Value Is" to "Formula Is" then in formula bar type: =AND($G$3="",$J$3="")
Condition2: "Cell Value Is" then "equal to" : =today()+3
in both condition click Format>Font>Color>Red

For G3:
Condition1: "Formul Is" : =$J$3=""
Condition2: "Cell Value Is" then "equal to" : =today()+3
in both condition click Format>Font>Color>Red


This one will work for example your date in F3 is Jul-28 since today(Jul-25) is 3days before F3 considering that G3&J3 are both non empty.
same way in G3 if your inut date is Jul 29 then tomorrow (Jul-26) it shall turn to red.

Turning into red is depending on your input dates and today's date.