Traffic Lights

[Branch man]

Hi everyone,

I am new to the site so I apologise in advance if I don't quite get it right first time round.

I have an Excel spreadsheet for mandatory training, the courses are listed in column A from A2 to A15 and have to be repeated at either 1, 3 or 5 yearly interval. I put the date on which the course was completed in column C3 and I would like to use the traffic light system (i.e. Green, Amber, Red) in column C4:

Green if the course is in-date.
Amber when the course is within 30 days of it's 1, 3 or 5 year point.
Red when the course has gone past it's 1, 3 or 5 year point.

I am aware that I need to use Conditional Formatting but that's as far as it goes.

Can anyone please help.

Regards

Branch man

[FDibbins]

Hi, welcome to the forum

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =A2 > =today()...format fill GREEN

Repeat 3 and 4...
=A2 < =today()-30...format fill Amber

Repeat 3 and 4...
=A2 < today()...RED