Groups

    Need help with Conditional formatting in MS-XL

    Scenario: I have a task recorded in an XL which is due on say 28th Feb. I want to use conditional formatting to highlight the row item in red, amber or green depending on when its due and the date today.

    if today is 25th Feb or before, task is due on 28th, row should be green
    if today is 26th or 27th Feb, task is due on 28th, row should be amber
    if today is 28th Feb or any date in March and task was due on Feb 28th, row should be red

    Can someone help with how to set this rule?

    3 Comments

    Not sure, but I would approach it by inserting a column with a formula deducting today() from the target date. Then you can set up conditional formating to say when the number is 3 or greater the cell is green, 1-2 amber, and 0 or less red. The conditional formatting can be on the target date cell but just 'pointing' towards the new calculation column which you can hide if you want. Hope this helps.

    Try something along the lines of

    Condition 1:-
    Greater than or equal to
    =today()
    Format Red

    Condition 2:-
    Greater than or equal to
    =today()-1
    Format Amber

    Condition 3:-
    Less than or equal to
    =today()
    Format Green

    The problem with doing this is that even if the task is complete, once it reaches the day before it will go amber and once it reaches and exceeds the target date, it will go red.
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Veteran TV host Sir Bruce Forsyth dies at 892529
      2. Veteran TV presenter doesn't die but gets damages from the Police - is that…66
      3. Just heard this...2 ★★★★★★★★★★★★★★ congrats to all on 392k ★★★★★★★★★★★★★★7764306
      4. ❅☁☁❅ I want☼to talk☼about the☔WEATHER☔no politics☃no religion❅☁☁❅18846211

      See more discussions