Conditional formatting based on column date

Options

Hello, I am trying to change the status of an action item to Red or Green based on the date it was completed.

The logic in my head, reads as follows (if (Completed>EndDate)) -> RED, else Green

but when using condition formatting , its not allowing me to select the EndDate column, instead its asking me to put a specific date.


I have attached a screen shot


Best Answer

  • AnthroTim
    AnthroTim ✭✭✭✭
    Answer ✓
    Options

    Hi,

    To do this you'll need an extra column that calculates that a task is late or not. You can target the cell you want to highlight or the whole row using conditional formatting.

    You can keep the column hidden and just have it as a helper column or perhaps set it as a flag, so it's clear that the task is/was late.


    The formula in the late column is: =IF([Complete Date]@row > [End Date]@row, 1, 0)

    And the conditional formatting rule is:

    Hope that helps!

    Tim

    anthrotechnic.com

Answers

  • AnthroTim
    AnthroTim ✭✭✭✭
    Answer ✓
    Options

    Hi,

    To do this you'll need an extra column that calculates that a task is late or not. You can target the cell you want to highlight or the whole row using conditional formatting.

    You can keep the column hidden and just have it as a helper column or perhaps set it as a flag, so it's clear that the task is/was late.


    The formula in the late column is: =IF([Complete Date]@row > [End Date]@row, 1, 0)

    And the conditional formatting rule is:

    Hope that helps!

    Tim

    anthrotechnic.com

  • TDXEV
    TDXEV ✭✭✭
    Options

    Thank you for the help. I was able to add the helper column and highlight the completed column.

    How do you get the FLAG? in the conditional formatting, I could not figure out how to say if Late is Flagged.

    I Just said if Late equals to 1