conditional formatting for dates in another column

Chasity
Chasity ✭✭
edited 05/23/23 in Formulas and Functions

I need to make the dates in a column turn green or red depending if they are complete. There is a box of due date of the deck, the deck completion date needs to turn either green or red, based off hitting the due date or missing it- OR if the box hasn't been filled with a date when it is due. HELP, please.

Answers

  • Hi @Chasity

    The way I would personally do this is to set up a checkbox column, then use a column formula to check the rows you want to be red:

    =IF([Due Date]@row = "", 0, IF(OR([Deck Completion Date]@row > [Due Date]@row, [Deck Completion Date]@row = ""), 1))

    Then you can set up Conditional Formatting on those checkboxes:

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭

    @Chasity

    Rather than try and explain in words here, I have created a very quick sample sheet of how you can get the right colours.

    Set up a helper column with an IF statement which would return the colour you want the date to go, then use that helper column in the conditional formatting box. I have shown the formula I used in a column but I had to surround it with " " to get it to show (and not perform!) you don't need the " " around the formula...

    I hope this helps

    Kind regards

    Debbie

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!