conditional formatting & Icon Set

[Fahad]

Dear;

I have a table in excel 2007. this table consist of the following coloms:

Task Name Start Date End Date Status
contact Vendor A 10-Jul-08 19-Jul-08 in-Progress
contact Vendor B 05-Jul-08 12-Jul-08 completed

I want to utilize the new conditional formatting feature (Icon Set) as follow:

IF (End date > Today date) then set a green flag on task name field
else if (End date = Today date AND Status=in-progress) then set a RED flag
on task name field
else if (End date < Today date AND Status=completed) then set a green flag
on task name field

I want to use the built in function Date() for comparison.

how can I do such formula? please help

 

[Bob Greenblatt]

Dear;

I have a table in excel 2007. this table consist of the following coloms:

Task Name Start Date End Date Status
contact Vendor A 10-Jul-08 19-Jul-08 in-Progress
contact Vendor B 05-Jul-08 12-Jul-08 completed

I want to utilize the new conditional formatting feature (Icon Set) as follow:

IF (End date > Today date) then set a green flag on task name field
else if (End date = Today date AND Status=in-progress) then set a RED flag
on task name field
else if (End date < Today date AND Status=completed) then set a green flag
on task name field

I want to use the built in function Date() for comparison.

how can I do such formula? please help

First, you DO NOT want to use the function Date()! You need to set up three
conditions. Assuming the table above begins in A1, In the conditional format
for A1 enter three conditions:
=C2>TODAY() for the first condition and set the format to what ever you want
Second condition:
=AND(C2=TODAY(),D2="In-Progress") and set the format to red
Third condition:
=AND(C2=TODAY(),D2="Completed") and set that to green.

You can then highlight A1 press the paint brush icon and then click and drag
through the remainder of the Task name column.

 

[Fahad]

Dear Bob

thanks for your support...

here what I did, I pointed on cell A1 (Task Name) , then I clicked the
conditional formatting and chosen from the menu Icon Sets. then I chosen the
3 Flags icons.
I clicked the conditional formatting again and chosen Manage rules. the
conditional formatting Rules Manager appeared and I chosen the rule just
created and edited.
in the edit formatting rule wizard, in the selecte a rule type window I
chosen format all cells based on their values.

here I noticed that there are only two fields to insert the formulas. I
entered the formulas you suggested as follow:
1st formula field: =$H$2>TODAY()
2nd formula field: =AND($H$2=TODAY(),$J$2="In-Progress")

I clicked Ok but nothing appear on the excel sheet. it seems I missed
something.

could you please advice.

 

[Bob Greenblatt]

Dear Bob

thanks for your support...

here what I did, I pointed on cell A1 (Task Name) , then I clicked the
conditional formatting and chosen from the menu Icon Sets. then I chosen the
3 Flags icons.
I clicked the conditional formatting again and chosen Manage rules. the
conditional formatting Rules Manager appeared and I chosen the rule just
created and edited.
in the edit formatting rule wizard, in the selecte a rule type window I
chosen format all cells based on their values.

here I noticed that there are only two fields to insert the formulas. I
entered the formulas you suggested as follow:
1st formula field: =$H$2>TODAY()
2nd formula field: =AND($H$2=TODAY(),$J$2="In-Progress")

I clicked Ok but nothing appear on the excel sheet. it seems I missed
something.

could you please advice.

OK, I just checked this with Excel 2007. You can NOT use the three flag set
to do what you want. You must instead use a formula to determine which cells
to format. You then need to enter this 2 more times each with the formulas
in my original message. For each formula, then select the formatting that
you want to appear when the condition is true.