Change cell Colour when a number of days have been passed
 

I have a spreadsheet and am looking for some assistance to make a conditional
format to produce the following:

I will be entering dates in a column and then between 1 and 7 days an
adjacent cell will show green and the words "Processing"

Between 8 days and 14 days the adjacent cell should turn yellow and show
"Check Status"

If the period exceeds 14days then the cell should turn red and show the word
"Overdue"

I have tried a number of the responses in the threads but just cannot seem
to get the proper result.

Thanks in advance

Change cell Colour when a number of days have been passed
 

Say Column A contains the dates and B contains the conditional formatting.

In B1 enter this formula
=IF(TODAY()-A1<7,"Processing",IF(TODAY()-A1<14,"Check Status","Over Due"))

copy down as needed
select column B and select Format-conditional formatting
Select "Cell Value Is" and "Equal to" and enter
="Processing"
change the format to a green background color
Select Add and do the same (except choose different colors) for
="Check Status"
and
="Over Due"

click okay


"SR7133" wrote:

I have a spreadsheet and am looking for some assistance to make a conditional
format to produce the following:

I will be entering dates in a column and then between 1 and 7 days an
adjacent cell will show green and the words "Processing"

Between 8 days and 14 days the adjacent cell should turn yellow and show
"Check Status"

If the period exceeds 14days then the cell should turn red and show the word
"Overdue"

I have tried a number of the responses in the threads but just cannot seem
to get the proper result.

Thanks in advance

Change cell Colour when a number of days have been passed
 

Thank you very much for that - I have done as you say but there are a couple
of points which I cannot resolve:
1. The "over 14days" is not turning the cell red
2. Column B shows "Overdue" in all the cells when there are no dates in
Column A

Can you advise how to resolve this please?

"Sloth" wrote:

Say Column A contains the dates and B contains the conditional formatting.

In B1 enter this formula
=IF(TODAY()-A1<7,"Processing",IF(TODAY()-A1<14,"Check Status","Over Due"))

copy down as needed
select column B and select Format-conditional formatting
Select "Cell Value Is" and "Equal to" and enter
="Processing"
change the format to a green background color
Select Add and do the same (except choose different colors) for
="Check Status"
and
="Over Due"

click okay


"SR7133" wrote:

I have a spreadsheet and am looking for some assistance to make a conditional
format to produce the following:

I will be entering dates in a column and then between 1 and 7 days an
adjacent cell will show green and the words "Processing"

Between 8 days and 14 days the adjacent cell should turn yellow and show
"Check Status"

If the period exceeds 14days then the cell should turn red and show the word
"Overdue"

I have tried a number of the responses in the threads but just cannot seem
to get the proper result.

Thanks in advance

Change cell Colour when a number of days have been passed
 

1. Check that the output of the cell and the conditional format condition
are the same. I accidently made overdue two words "over due". This might be
where your error came from.

2. Change your formula to
=IF(A1=0,"",IF(TODAY()-A1<7,"Processing",IF(TODAY()-A1<14,"Check
Status","Overdue")))

"SR7133" wrote:

Thank you very much for that - I have done as you say but there are a couple
of points which I cannot resolve:
1. The "over 14days" is not turning the cell red
2. Column B shows "Overdue" in all the cells when there are no dates in
Column A

Can you advise how to resolve this please?

"Sloth" wrote:

Say Column A contains the dates and B contains the conditional formatting.

In B1 enter this formula
=IF(TODAY()-A1<7,"Processing",IF(TODAY()-A1<14,"Check Status","Over Due"))

copy down as needed
select column B and select Format-conditional formatting
Select "Cell Value Is" and "Equal to" and enter
="Processing"
change the format to a green background color
Select Add and do the same (except choose different colors) for
="Check Status"
and
="Over Due"

click okay


"SR7133" wrote:

I have a spreadsheet and am looking for some assistance to make a conditional
format to produce the following:

I will be entering dates in a column and then between 1 and 7 days an
adjacent cell will show green and the words "Processing"

Between 8 days and 14 days the adjacent cell should turn yellow and show
"Check Status"

If the period exceeds 14days then the cell should turn red and show the word
"Overdue"

I have tried a number of the responses in the threads but just cannot seem
to get the proper result.

Thanks in advance

Change cell Colour when a number of days have been passed
 

Dear Sloth

That did the trick - thank you very much for your assistance - much
appreciated

"SR7133" wrote:

Thank you very much for that - I have done as you say but there are a couple
of points which I cannot resolve:
1. The "over 14days" is not turning the cell red
2. Column B shows "Overdue" in all the cells when there are no dates in
Column A

Can you advise how to resolve this please?

"Sloth" wrote:

Say Column A contains the dates and B contains the conditional formatting.

In B1 enter this formula
=IF(TODAY()-A1<7,"Processing",IF(TODAY()-A1<14,"Check Status","Over Due"))

copy down as needed
select column B and select Format-conditional formatting
Select "Cell Value Is" and "Equal to" and enter
="Processing"
change the format to a green background color
Select Add and do the same (except choose different colors) for
="Check Status"
and
="Over Due"

click okay


"SR7133" wrote:

I have a spreadsheet and am looking for some assistance to make a conditional
format to produce the following:

I will be entering dates in a column and then between 1 and 7 days an
adjacent cell will show green and the words "Processing"

Between 8 days and 14 days the adjacent cell should turn yellow and show
"Check Status"

If the period exceeds 14days then the cell should turn red and show the word
"Overdue"

I have tried a number of the responses in the threads but just cannot seem
to get the proper result.

Thanks in advance