Traffic Light Conditional Formatting Help

[mike.lewis]

Hi There

i am setting up some traffic light alerting in excel 2010
my issue is around getting the "lights" to respond to my desired range.

basically what i need is for the range to be green for anything below 1 min ( or we can work off 15 min intervals if easier ) then anything above one minute red.
The ideal format i want to use is hh:mm, i can even go with h:mm ( so 1.25 for 1 hour 15 minutes )

Help !!!!

 

[Andrew Poulsom]

You can use green, >= with the formula ="01:00"+0 and red with >0.

 

[mike.lewis]

Thanks Andrew, i have tried this and when i enter 0:15, 0:30, 0:45 it work great and displays red, however as soon as i hit what i would consider hours so 1:00 im hitting green, basically 0 should be green anything above red. appreciate your help

 

[Andrew Poulsom]

For me anything less than 1 minute is red and anything 1 minute and above is green, which is what you asked for.

 

[mike.lewis]

For me anything less than 1 minute is red and anything 1 minute and above is green, which is what you asked for.

Hey Andrew, maybe a misunderstandin
In my intial thread i have placed

basically what i need is for the range to be green for anything below 1 min ( or we can work off 15 min intervals if easier ) then anything above one minute red.
The ideal format i want to use is hh:mm, i can even go with h:mm ( so 1.25 for 1 hour 15 minutes )

Hope this is clearer, any ideas

 

[Andrew Poulsom]

There is no misunderstanding. The conditional formatting rules I posted work for me. If they don't work for you post the detailed steps you took.

 

[mike.lewis]

In the conditional formattting box i have the following

Icon = green tick.. when value is >= value ="01:00"+0 Type number
Icon = Red cross.. when < formula and > value 0 Type number
Icon = when <=0

if i enter in the cell 0:00 then come back with no icon ( looking for green tick )
0:15 through to 0:45 comes with red cross ( this is fine )
as soon as i hit 1:00 or above, so 1:15, 1:30 or even 2:45 ( however high i go ) it shows a green tick ( should be red )

 

[Andrew Poulsom]

Looking again try:

Icon = red cross when value is >= Value ="01:00"+0 Type Formula
Icon = green tick when < Formula and > Value 0 Type Number
Icon = green tick when <= 0

 

[mike.lewis]

Thank Andrew
alot closer, so when i type in 0 it hits a green tick = Great
When i type 0:25 - 0:50 - 0:75 - green tick - need red
1:00 and above is sweet and gives the red cross

so at this stage it works fine, however when i am doing between 0 and 1 ( .25, .50 ) it is not quite there

 

[Andrew Poulsom]

You said "basically what i need is for the range to be green for anything below 1 min". So why should it now be red? Just change the icons until you get what you want.