In this post, I’ll share how to set up traffic lights in Excel to show variances between a scheduled date and the actual date. This post in response to a comment Stephen wrote from an earlier post I wrote called How to format dates that are over 1 year old to turn red in Excel using conditional formatting.
Hi I am trying to create a spreadsheet using traffic lights in (2010) to show when a document review date has expired. One column will show the actual date of review, the next will shown the scheduled review date and the next column will contain the traffic lights,
green if carried out before or on the scheduled review date,
Amber if between 1 and 30 days overdue and
red if over 30 days overdue.
At the time of review the actual date column will then be updated to reflect the new date . The scheduled review column will also be updated to show next expected review date. Can you advise me best way to handle this please.
I would arrange the spreadsheet as seen below.
Then I would create a formula that subtracts the actual date from the scheduled date. For example =C2-B2 would be -36 days in D2 cell because the actual date happened 36 days before he scheduled date. If the scheduled date and actual date were the same the formula value would be zero. And if the actual date was after the scheduled date it would display in how many days.
Then select all the cells you want the conditional formatting rule of traffic lights on. Then click the Home tab–>Conditional Formatting–>New Rule.
Make the following adjustments:
- Change the drop down in Format Style to Icon Sets.
- Select the Icon Style to be the traffic lights.
- Click Reverse Order
- Red traffic light is when value is greater than or equal to 30 number.
- Yellow traffic light is when less than 30 and greater than or equal to 1 number.
- Green traffic light is when less than 1 number.
At this point, you will see the traffic lights and the values of the formulas in column D. So the trick is to make the font change to white.
And the final result:
Download my example file to practice with at this link traffic lights conditional formatting.