+ Reply to Thread
Results 1 to 6 of 6

Conditional formatting and the traffic light color coding for forecast and actual dates

  1. #1
    Registered User
    Join Date
    01-30-2015
    Location
    Phoenix
    MS-Off Ver
    MS Office 2010
    Posts
    21

    Conditional formatting and the traffic light color coding for forecast and actual dates

    In the attached file, I’m trying to utilize conditional formatting and the traffic light color coding for: FCST dates out over seven days (White), FCST dates past due (RED), FCST dates within seven days out (YELLOW) and actuals where FCST and ACT are both date populated (GREEN). I can’t seem to figure out the formula to make the actuals green where a date is populated for both the FCST and ACT columns for all tasks. The formula I’m using for the green color coding is not working because I’m sure it’s not correct. Any help you could provide would be appreciated.Here's a file where the conditional formatting starts to fall apart for actuals around columns AX/AY where the FCST columns are green and the ACT columns red.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,972

    Re: Conditional formatting and the traffic light color coding for forecast and actual date

    Using the full range like that for your CF, Im not sure you can select 2 adjacent column sets to test like that

    unless you mean that ALL sets need 2 dates in them - ie all dates in the row are filled? IF that is the case, maybe include a count(B2:HG2)<214 into the CF rules?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-30-2015
    Location
    Phoenix
    MS-Off Ver
    MS Office 2010
    Posts
    21

    Re: Conditional formatting and the traffic light color coding for forecast and actual date

    The ACT or actual task columns are only populated with a date when complete. This is a quick color visual to see the status of all tasks on a project that are actualized or not complete and still forecasted. If not complete, then the forecast (FCST) dates reflect if a task is past due, due in the next 7 days or due out +7 days.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,972

    Re: Conditional formatting and the traffic light color coding for forecast and actual date

    So is your intent to apply CF if ALL the cells in a row have dates in them (for both FC and ACT?)

  5. #5
    Registered User
    Join Date
    01-30-2015
    Location
    Phoenix
    MS-Off Ver
    MS Office 2010
    Posts
    21

    Re: Conditional formatting and the traffic light color coding for forecast and actual date

    Yes, CF for GREEN color coding where FCST and ACT are both populated with a date. But, CF for WHITE, YELLOW and RED color coding when a date is populated in the FCST columns and the ACT is not populated with a date.

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,504

    Re: Conditional formatting and the traffic light color coding for forecast and actual date

    I am coming up with below logic:

    Assuming that column title of Forcast allways end with "FCST" (Otherwise, MOD(COLUMNS($A:B),2)-1 will be used to define)

    1) Green is most popuplar for both ACT and FCST: set whole range Green as default.

    2) Set blank cells and FCST cells (>7 days from today) to White:
    =OR(ISBLANK(B2),AND(RIGHT(B$1,4)="FCST",B2>TODAY()+7))

    3) Set colour to FCST cells:

    Red:
    =AND(RIGHT(B$1,4)="FCST",B2<TODAY(),C2="")

    Yellow:
    =AND(RIGHT(B$1,4)="FCST",B2>=TODAY(),B2<=TODAY()+7)
    Attached Files Attached Files
    Quang PT

+ 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. [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
  2. Traffic Light Conditional Formatting
    By hzaz in forum Excel General
    Replies: 4
    Last Post: 05-01-2013, 02:10 PM
  3. 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
  4. Conditional Formatting - Traffic light dates
    By zx561 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-19-2012, 02:51 PM
  5. Replies: 4
    Last Post: 11-08-2007, 11:53 AM

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