conditional formating using cells containg dates
 

I have a spread sheet that contains 3 date columns, column A is open date,
column F is due date, and column G is complete date, and column H is Status
(RED,YELLOW,GREEN). I want column H to change to RED when the due date is
overdue, to change to YELLOW when the due date is within 5 days of being
overdue, and to change to GREEN when 6 days or less of the due date. How can
I do this? The formula that I have in the cell is
=IF(F9="","",IF(F9="",IF(TODAY()F9-5,"RED",""),IF(TODAY()=F9-5,"YELLOW","GREEN")))

Select all your cells in column H, say starting in row 2
Go to CF (FormatConditional Formatting)
Change Condition 1 to Formula Is
Input =F2<=TODAY()
Click Format
Select pattern
RED
OK
Add condition
Change Condition 2 to Formula Is
Input =F2<=TODAY()+5
Click Format
Select pattern
YELLOW
OK
Add condition
Change Condition 1 to Formula Is
Input =F2<=TODAY()+6
Click Format
Select pattern
GREEN
OK
OK

--
HTH

Bob Phillips

"Roy" wrote in message
...
I have a spread sheet that contains 3 date columns, column A is open date,
column F is due date, and column G is complete date, and column H is
Status
(RED,YELLOW,GREEN). I want column H to change to RED when the due date is
overdue, to change to YELLOW when the due date is within 5 days of being
overdue, and to change to GREEN when 6 days or less of the due date. How
can
I do this? The formula that I have in the cell is

=IF(F9="","",IF(F9="",IF(TODAY()F9-5,"RED",""),IF(TODAY()=F9-5,"YELLOW","G
REEN")))

Thanks, that worked. Now I want RED, YELLOW, or GREEN to be automaticlly
inserted for the respective color. How do I do that?

"Bob Phillips" wrote:

Select all your cells in column H, say starting in row 2
Go to CF (FormatConditional Formatting)
Change Condition 1 to Formula Is
Input =F2<=TODAY()
Click Format
Select pattern
RED
OK
Add condition
Change Condition 2 to Formula Is
Input =F2<=TODAY()+5
Click Format
Select pattern
YELLOW
OK
Add condition
Change Condition 1 to Formula Is
Input =F2<=TODAY()+6
Click Format
Select pattern
GREEN
OK
OK

--
HTH

Bob Phillips

"Roy" wrote in message
...
I have a spread sheet that contains 3 date columns, column A is open date,
column F is due date, and column G is complete date, and column H is
Status
(RED,YELLOW,GREEN). I want column H to change to RED when the due date is
overdue, to change to YELLOW when the due date is within 5 days of being
overdue, and to change to GREEN when 6 days or less of the due date. How
can
I do this? The formula that I have in the cell is

=IF(F9="","",IF(F9="",IF(TODAY()F9-5,"RED",""),IF(TODAY()=F9-5,"YELLOW","G
REEN")))