+ Reply to Thread
Results 1 to 5 of 5

Conditional formatting traffic light formula

  1. #1
    Registered User
    Join Date
    07-21-2013
    Location
    london england
    MS-Off Ver
    Microsoft Office 365
    Posts
    87

    Conditional formatting traffic light formula

    I have 3 columns; Start Date, Target Date & Actual Number Of Working Days for a project. I want to condition format column C so that if it has over-run the target date it should show the red traffic icon, if it is spot on the target date it should show the amber traffic icon and if it is under the target date it should show the green traffic icon. If there is no start date the rest of the row should be blank. In this case I want the conditional format to remain blank. Please note I want to see the traffic icons and also the number of working days as well on the same cell, I don't want the traffic light icons to replace the numbers. So in the example below, C1 should be green, C2 should be red, C3 should remain blank, C4 should be amber. Thanks for any help I get.
    A (Start Date) B (Target Date) C (Actual Number Of Working Days)
    1 2/6/14 10/6/14 10
    2 2/6/14 10/6/14 5
    3
    4 2/6/14 10/6/14 7

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Conditional formatting traffic light formula

    find the attached file
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    07-21-2013
    Location
    london england
    MS-Off Ver
    Microsoft Office 365
    Posts
    87

    Re: Conditional formatting traffic light formula

    Hi nflsales, thanks for your help. Unfortunately when I put your conditional format formula into my spreadsheet it doesn't seem to work. I don't know if it is because on my sheet the working days column there is actually a formula which produces the number of days.

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Conditional formatting traffic light formula

    Attach your worksheet

  5. #5
    Registered User
    Join Date
    07-21-2013
    Location
    london england
    MS-Off Ver
    Microsoft Office 365
    Posts
    87

    Re: Conditional formatting traffic light formula

    The columns I am interested in are EQ, ER & ET.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Traffic Light Conditional Formatting based on Text
    By danee in forum Excel General
    Replies: 2
    Last Post: 11-05-2013, 11:27 AM
  2. [SOLVED] How to use traffic light conditional formatting icon set with dates.
    By rbac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-21-2013, 08:19 PM
  3. Traffic Light Conditional Formatting
    By hzaz in forum Excel General
    Replies: 4
    Last Post: 05-01-2013, 02:10 PM
  4. Need help with traffic light conditional formatting icon set
    By jjospeh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-21-2012, 03:33 PM
  5. Conditional Formatting - Traffic light dates
    By zx561 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-19-2012, 02:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1