Traffic light system for dates

[rmjonny]

I apologise if this has been asked before but i couldnt find what im looking for.

Basically i am looking to create spreadsheets for all my yearly checks. I have 3 columns and upto 100 rows, and i would like the date colums to be on a traffic light system whereby it is green if it is more than 10 months from date trained, to amber for 11 months and red for 11 months 2 weeks

[TABLE="width: 277"]
<tbody>[TR]
[TD="width: 64"]Ser.[/TD]
[TD="width: 64"]Name[/TD]
[TD="width: 149"]Date Trained/Refreshed[/TD]
[/TR]
</tbody>[/TABLE]
1 Steve 1/1/16
2 Jane 1/2/16
3 Gary 1/3/16

I hope i have explained this properly and really appreciate any help i can get with this

 

[chrism216]

There is a traffic light in the conditional formats.

 

[rmjonny]

yeah but i am a real computer biff and cant seem to make it work

 

[Eric W]

Try this:

1) Select the column with the date.
2) Click Conditional Formatting > Icon Sets > Select the Traffic Lights
3) With that column still selected, click Conditional Formatting > Manage Rules > select the rule you just added > click Edit Rule
4) You'll see the 3 icons on the bottom left. For the Green icon, change the Type to formula. Then change the Value to =TODAY()-300
5) For the Yellow icon, change the Type to formula, then change the Value to =TODAY()-340
6) Click OK, twice.

That should do it. Let me know how it works.

 

[rmjonny]

Eric Sir, you are a legend! Thank you so much mate!

 

[Eric W]

Glad to help!

 

[fbacchus]

Is there a way to use the traffic light if the cell contains a R, G, Y (Red, Green, Yellow (Amber)? And how do I get a blue traffic light to represent "Completed"?