conditional formatting for dates in another column

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.


  • Genevieve P.
    Genevieve P. Employee Admin

    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:



  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭


    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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!