How to Use Excel Traffic Lights with Conditional Formatting Dates

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.

Stephen’s comment:

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.

xcel formulas with dates actual minus scheduled variances

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.

Conditional Formatting New Rule

Make the following adjustments:

  1. Change the drop down in Format Style to Icon Sets.
  2. Select the Icon Style to be the traffic lights.
  3. Click Reverse Order
  4. Red traffic light is when value is greater than or equal to 30 number.
  5. Yellow traffic light is when less than 30 and greater than or equal to 1 number.
  6. Green traffic light is when less than 1 number.

Icon Sets Rules Manager Excel

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.

Change font to white trick

And the final result:

Traffic lights Excel Conditional Formatting

Download my example file to practice with at this link traffic lights conditional formatting.

About Steve Chase

I'm a proud husband and dad to 4 boys! Microsoft Certified Trainer and Boy Scout Leader are some of things I call myself. The Cincinnati Reds are my favorite team! When not outdoors, I enjoy working with documents in Microsoft Office, QuickBooks, Visio and Photoshop. My wife, Erin, shares her awesome recipes on her blog at
This entry was posted in Excel and tagged . Bookmark the permalink.

33 Responses to How to Use Excel Traffic Lights with Conditional Formatting Dates

  1. Stephen says:

    Hi what formula would I use if I have a column indicating the number of years and I would like green traffic light between 1 and 8 years, Amber traffic light for 9 years and red traffic light for 10 years and above. Thank you

    • Steve Chase says:

      I think you can get want you need with doing custom icon sets. You can do red greater than or equal to 10. Amber when less than and greater than or equal to 9. I don’t see an option in the rules to do the middle icon to equal a number exactly like 9. The green icon would be when less than 9. I like to use the INT function which you put in front of the date formula. It will round it down to the nearest integer. Example =INT(new date – old date)/365.

      I hope this helps. I’m hope I’m understanding your question properly.

      • Stephen says:

        Thanks for your reply, the old date would be the date of joining the group in B2, and today’s date would be in C2. The length of service eg number of years, would be in D2 with traffic lights showing as you you suggested red greater than or equal to ten etc. based on this information can you advise me what details should be entered for (new date – old date).
        Thank you

      • Steve Chase says:

        Stephen, I apologize but I’m not sure I completely understand the request. Are we trying to get the formula inputed? Or is it the conditional formatting, or both.

        Is C2 always today’s date? What about C3, C4, and so on? If so perhaps we can link that to the TODAY() function.

        I’m thinking the formula would look like this in D2. =(B2-TODAY())/365.

        Also might be =(B2-C2)/365.

        Does either formula work for your file?


  2. Boj says:

    instead of using the white font color to hide the date. Guess you didnt know about the check box for show icon only. works.

  3. Latha says:


    Please can anyone help me how to create Leave Planner Template with the help of Traffic Lighter?

    • Latha says:

      The condition would be for example: if Team Size is 10 including the buffers (2), TL can approve leave at a time only for 2, the colour for 2 persons of that perticular cell should be GREEN and when 3rd person updates for a leave in template the colour should be YELLOW and more than 3 person the colour of the cell should be RED

      • Steve Chase says:

        I’m not sure I understand what you are going for. First in order to do the Icon Sets as traffic lights we need a column of numbers. Either the numbers will be typed in directly or they can be the result of a formula you write. After you can generate the numbers, there is a feature that can hide the numbers and display just the traffic lights when you check the box “Show Icon Only”. After you have the number column select the cells, then go to Conditional FormattingNew Rule. Format Style will be Icon Set Style is a drop down and has Traffic lights. The main thing to do while setting up the rule is to change the Type drop down from percentage to Number. For example Green Traffic Light when value is greater than or equal to 8 “Number”. Yellow Traffic Light when less than or equal to your number. Red Traffic light when less than your number. If you need to make edits, select the conditional formatting button then choose manage rules either with the selected cells or entire worksheet. The conditional formatting part should be pretty smooth, but maybe harder will be writing the formula underneath. It might require using some logic functions including the IF, AND, and OR functions.

  4. Scott says:

    I found your post via Google, and was hoping you’d be able to assist me; I have a table that I created from aggregating data from additional sheets (within the same workbook), and manipulating them before adding it to my table (for example, I get the sentence “Ahead of Schedule” and write a “1” into my main table. The range of possible values I use are 1, 0 and -1.

    However, when I attempt to use the Stoplight Icon Sets (in Excel 2007), the icons do not display.

    I originally thought the issue was in my formatting, as it is currently on “General”. I switched it do Number (by using the dropdown in the menu), but that didn’t seem to help. I also tried modifying the stoplight conditions and referencing the type in Edit Format Rule to Number, to no avail.

    Any thoughts? Thanks!

    • Steve Chase says:


      I have seen before where the numbers 0 and 1 were not picked up from a vlookup formula. I remember using the INT formula on the next column. The INT formula rounds the number down to the nearest integer so you would return -1, 0 or 1.
      The second issue is to make sure that you haven’t got too many overlapping conditional formatting rules on top of each other. Clicking manage rules will notify of how many rules there are and if necessary remove any extras that shouldn’t belong.

      Third, you could create a test area to the side in which you are able to generate the proper icon sets. Then use the format painter to copy the conditional formatting over top of your desired place.


  5. Bonnie says:

    Hello! This was very helpful. I was able to get almost what I wanted but would like to have the lights show Red, Yellow, Green based on 2 columns. I think I may need nested If statement?

    Column C – Due Date
    Column D – Completed Date
    Column E – Percentage Complete
    Column F – Status (this is where the light shows)

    I have a formula in the status to show a # based on Column D – C, if C is blank, then use Today()-C
    This works great.

    I want to add another condition to so that if the # of days is -2 (so it’s close to our deadline) AND the % complete is less than 75% then have the status of YELLOW.

    That way, if it’s -2 but the percentage is 100% show green, if less than 75%, then yellow

    I hope that makes sense!

    Thank you,

    • Steve Chase says:

      Hi Bonnie,
      I’m not sure exactly how one could use the icon sets to accomplish this task since you want multiple conditions. It does sound like the use of the IF function along with the AND($F2=-3,$E2<75%). You could try the "use a formula to determine which cells to format" option and fill it with a yellow background color instead of an icon.

      Hope you can find a solution.


  6. kPev says:


    Now this is quite awesome – but how now to mail merge the traffic lights into Word or Publisher please? Is it possible?
    I’m trying to create a scorecard for about 500 stakeholders with a traffic light but output in a nice form for them….

    • Steve Chase says:

      I do not know if it is possible to mail merge graphics. You almost would need to create the report in Excel, might be tricky. But there is a page break button that might work. Depends on a lot of how much time is it to you to create the report. Sorry I don’t have any solid solutions. If you do find a workaround, please share it with this community.

  7. Angie says:

    Hi I was wondering if you could help. I want the light to turn green if the value is less than or equal to 0 and Red if its greater than 0. Please help. Thanks

  8. Melvin says:

    Hi Steve,

    If i want to change the following formula result showing “increase” or “decrease” to icons, it contains the following “=IF(B6>B5,’look up’!$B$13,’look up’!$B$14)”. What methods will be suitable to make it change using conditional formatting?


    • Steve Chase says:

      Since the icon sets work with number ranges, I would say that you probably would need to create a duplicate column where your formula with “increase” or “decrease” would display. Another idea is to make the icon sets “display only” which hides the numbers and shows icon only and make that a very small column width. Then link the numbers and text you want to an adjacent column. You can use the & to concatenate the numbers with text.

  9. Pingback: How To Arrange Dates In Excel | Information

  10. Mona Whitley says:

    Hi Steve. I
    I have a very simple formula for my traffic lights which simply subtracts the “Actual Date” from the “Planned Date”, and the traffic lights (which I have named Status) would become Red/Yellow/Green based on the following formula:
    Red when >= 14
    Yellow when = 7
    Green when < 7

    My issue is when I initially load an item into my spreadsheet, the Actual Date will be blank (because it hasn't happened yet). Currently, my indicator gives me ######, because there's an error when trying to subtract a 'blank cell' in the Actual Date cell from the Planned Date. What I want is for the indicator for the Status to remain clear (blank) if there is no date loaded into the Actual Date cell. Do you have any idea how I can make that happen? Perhaps a nested IF statement? I'm not savvy in writing IF statements. Do you think you could offer a suggestion for how to write such an IF statement that first looks to see if there is an empty cell for "Actual Date" and if that is TRUE, then it makes the Status cell value equal zero — producing no indicator. Else, if the "Actual Date" cell is not empty, then it conducts the normal formula noted above to produce a RED/YELLOW/GREEN icon in Status column.

    Appreciate your help and quick response.

    • Mona Whitley says:

      Correction: When the formula produces zero, the indicator is Green (which it should be).

      I need to be able to have an empty Status (no icon displayed) if there is no information in EITHER “Actual Date” or “Planned Date”.


  11. Steve Chase says:

    Hi Mona,
    You can use an IF function with an OR to test if a cell is blank. The way that we check for a blank is “”. That is two double quotes back to back without a space. So you formula would look something like this =IF(OR(A2=””,B2=””,””,B2-A2). This is where A2 is the planned date and B2 is the Actual date. If either the planned date or the actual date is blank then the if function will return a value of a blank value and the icon sets should not appear blank. Here is a sample Excel file you can view with my OneDrive folder.!AmOflzuTYrYwmPps1BNhZv-LNdWx6w

    • Mona says:

      Hello Steve.
      Apologies it took a while to get back to you. However, your post was quite helpful.

      A decision changed my initial direction, and I instead needed to show the indicator if the task closed on time or late. So, same concept as above, wherein I needed the indicator to be blank if either the Actual Finish Date or the Planned Finish Date cells were blank for any reason. Prior to your input, I didn’t know how to write the nested IF/OR statement at all……. but I got what I needed to work following your example. I did have to alter a bit for mine to work correctly (per my new direction) as follows:
      Actual Finish Date]]-[@[Risk ORIGINAL Planned Finish Date ‘[OPFD’]]]))

      Thanks Much. I’m a happy camper. 🙂

  12. Rachel Hair says:

    Hi Steve,

    This information is great thank you, especially your example in the OneDrive folder.

    Along the lines of Mona Whitley’s question above, my question again concerns when the due or completion date is blank, however I would like a progress status indicator if possible to confirm if it is on track i.e. yellow if the completion date is blank but still within the due date, or red if the completion date is blank but over the due date.

    For example – I have a due date (A), completion date (B) and status (C). Status should show as follows:
    Yellow – blank completion date (B) not yet due (A)
    Red – blank completion date (B) past due date (A) OR completed date (B) past due date (A)
    Green – completion date (B) before or on due date (A)


  13. John says:


    How come when the scoring for orange traffic light is set at 79%, but remains on 79% and only changes to orange on 78% ? is this the rounding up of the scoring ?


  14. John says:

    *but remains green on 79%…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s