ICON traffic light system

[Danielle1]

Hi All,

Hoping someone can help with a conditional formatting question.
[TABLE="width: 751"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]If the date completed is less than or equal to the date due, get the icon tick[/TD]
[/TR]
[TR]
[TD]If the date completed is greater than the due date get the amber exclamation mark [/TD]
[/TR]
[TR]
[TD]if a date of zero in inputted, get the red cross[/TD]
[/TR]
</tbody>[/TABLE]

Column 1 - Activity Title
Column 2 - Due Date
Column 3 - Date Completed

Thanks in advance!

 

[Gerald Higgins]

OK, there may be simpler ways, but this is how I would do it.

There are several steps to this.

1) Find the character you want to use for each icon.
2) Write a formula to handle the logic you want to apply, and include those characters in the formula.
3) Format the cell into the correct font, so that the icon appears as expected.
4) Apply conditional formating to the cell, to apply the colour.

1) For the cross and tick icons, you could use the Wingdings font, but that does not seem to have an exclamation mark symbol, so you might have to pick a different symbol.
Wingdings contains several tick and cross symbols - one of the tick symbol appears as
ü
in many other fonts - you can copy and paste the symbol from your pc's character map application if you're not sure about this.
And the corresponding cross symbol appears as
û
Note that these may look very similar but they are actually different.

2) The formula could be something like this, assuming that completion date is in A1, and due date is in B1.
=if(A1=0,"û",if(A1< = B1,"ü","ZZZ"))
Replace ZZZ with whatever character you choose to use for the exclamation mark.

3) Apply the wingdings font to the cell.

4) Apply conditional formating.
I'll keep that for a separate post if you don't mind, let's see how you get on with the first 3 parts.