Conditional Formatting
 

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

Conditional Formatting
 

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

In article ,
Pete Sperling wrote:

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

Conditional Formatting
 

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.
--
This post was created using recycled electrons!


"Pete Sperling" wrote:

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

Conditional Formatting
 

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

"Newbeetle" wrote:

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.
--
This post was created using recycled electrons!


"Pete Sperling" wrote:

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

Conditional Formatting
 

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


"JE McGimpsey" wrote:

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

In article ,
Pete Sperling wrote:

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

Conditional Formatting
 

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.

--
This post was created using recycled electrons!


"Pete Sperling" wrote:

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

"Newbeetle" wrote:

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.
--
This post was created using recycled electrons!


"Pete Sperling" wrote:

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

Conditional Formatting
 

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.
--
This post was created using recycled electrons!

Conditional Formatting
 

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

In article ,
Pete Sperling wrote:

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.