Color code the cells

Lahcene
Lahcene ✭✭
edited 11/27/23 in Formulas and Functions

Hi All,


I have a question regarding color coding the cells on Smartsheet.

How can I color code column (Actual Delivery date) to highlight the following:

Dark green : for when the Actual date is equale or less than Date committed

Light Green : for when the Actual Date is after the date committed

Red : for when Actual date is empty

Many thanks :)


Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You are going to need a helper column that can be hidden after getting it set up. I would suggest making it a dropdown column and using the various color descriptions as the options within the column settings. In this column you would use a formula to output each of the different color descriptions then finally use conditional formatting to actually change the cell colors.

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    @Paul Newcome jumped in before I got this posted, but I would use the same approach.

    And here is the formula for the format helper column:

    =IF(ISBLANK([Date committed]@row), "", IF(ISBLANK([Actual delivery date]@row), "Not Entered", IF([Actual delivery date]@row <= [Date committed]@row, "On Time", IF([Actual delivery date]@row > [Date committed]@row, "Late", ""))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!