Conditional Formatting

[Guest]

One Cell contains a Due Date, and a second cell contains a Completed Date.
1. I would like to have the background of the Due Date cell to change to
yellow only when the date in this cell is within one week of todays date and
the Completed Date cell is still null, otherwise the background remains white
or transparent.
2. I would like to have the background of the Due Date Cell to change to
red only when the date in this cell is older than todays date and the
Completed Date cell is null.

Any help greatly appreciated - Pete

 

[JE McGimpsey]

One way:

Assume the Due date in cell in A1, the Completed date in B1

CF1: Formula is =AND(LEN(B1)=0,A1<TODAY())
Format1: <patterns>/<red>

CF2: Formula is =AND(LEN(B1)=0,A1<(TODAY()+7))
Format2: <patterns>/<yellow>

 

[Guest]

You can do this in conditional formatting found on the format menu.

First thing though I would do in a cell thats not being used is type

=today()

This will then shows todays date, so say this is cell "A1"

Say Cell "B2" has the due date, and Cell "C2" has the completed date.

ok say you need cell b2 to change colour, select this cell, go format,
conditional format.


This is condition one, on the drop down menu select "Formula Is"

Then type in the following

=IF(AND(B2<$A$1,C2=""),TRUE)

Then click format button, click pattens and change the sample colour to red,
then ok.

Click the add button, your now see condition 2, again

This is condition two, on the drop down menu select "Formula Is"

Then type in the following

=IF(AND(OR(B2=$A$1,B2<=$A$1+7),C2=""),TRUE)

Then click format button, click pattens and change the sample colour to
yellow, then ok.

Then ok button.


Should now work as required.

 

[Guest]

Newbeetle - Your below works great - one more thing - how do I add a third
condition which leaves the Due Date cell with no color if there is no date in
the cell.

I'm trying to prevent having to add the conditions each time a new item is
created in the list.

Thanks for your assistance. - Pete

 

[Guest]

JE McGimpsey - Your suggestion works great - How would I add a third
condition which leaves cell A1 with no color if the Due date cell is null.
I'm trying to prevent having to enter the conditions in the Due date cells
each time I add a new action item to the list.

Thanks for your assistance - Pete

 

[Guest]

Hi,

you don't need to add another condition, instead change the formula as
follows;

Condition 1

=IF(AND(B2<$A$1,C2="",B2<>""),TRUE)

Condition 2

=IF(AND(OR(B2=$A$1,B2<=$A$1+7),C2="",B2<>""),TRUE)

if its a blank cell it should stay white.

 

[Guest]

Pete, a major benefit with the way JE McGimpsey has laid out the formula, is
you don't need to have a cell with Today() in it, thats just something I do,
as I tend to refer other cells to one throughout my worksheets, and give me
one reference to change for testing etc.

If you want to remove it, just modify the formulas too;


Condition 1

=IF(AND(B2<Today(),C2="",B2<>""),TRUE)

Condition 2

=IF(AND(OR(B2=Today(),B2<=Today()+7),C2="",B2<>""),TRUE)

Should work as required.

 

[JE McGimpsey]

CF1: Formula is =AND(LEN(B1)=0,A1<>"",A1<TODAY())
Format1: <patterns>/<red>

CF2: Formula is =AND(LEN(B1)=0,A1<>"",A1<(TODAY()+7))
Format2: <patterns>/<yellow>