How do I get a cell to change color based at certain increments based off of a date in a column?

PKane
PKane ✭✭✭✭
edited 05/05/22 in Formulas and Functions

I want to be able to change the color of date cells in a Smartsheet at 5 years and 9 years out from the install date in each row of a column.

One the 5 year mark is reached after install - it changes the cell to yellow, and at 9 years it turns that cell to red once the 9 year mark is reached


Not even sure where to start with this one - hopefully someone can help/advise.


Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @PKane

    Conditional Formatting.

    Open Conditional Formatting, click Add New Rule. Set your condition by selecting the column and defining custom criteria. So for your first column, select LFCM 5 years Post-Install, and for criteria select "is in the past." Then set the format, and select which columns to apply that format to. Add another rule for the 9 year post-install field, and then drag the 9 year rule above the 5 year rule. (This is because higher rules take precedence over lower rules. Therefore if it's 6 years post-install the field will be yellow because the 9 year post install rule is not yet true, but once you hit 9 years, that rule takes precedence so the field will be red.) Here you can see how it changes based on rule position.

    In the top box, some of my End dates are in the past, that rule is applied first, followed by the rule for Start date in the past.

    When I switch them, because all the start dates are in the past, all the fields are red, taking precedence over the rule for rows with End date in the past.


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @PKane

    Conditional Formatting.

    Open Conditional Formatting, click Add New Rule. Set your condition by selecting the column and defining custom criteria. So for your first column, select LFCM 5 years Post-Install, and for criteria select "is in the past." Then set the format, and select which columns to apply that format to. Add another rule for the 9 year post-install field, and then drag the 9 year rule above the 5 year rule. (This is because higher rules take precedence over lower rules. Therefore if it's 6 years post-install the field will be yellow because the 9 year post install rule is not yet true, but once you hit 9 years, that rule takes precedence so the field will be red.) Here you can see how it changes based on rule position.

    In the top box, some of my End dates are in the past, that rule is applied first, followed by the rule for Start date in the past.

    When I switch them, because all the start dates are in the past, all the fields are red, taking precedence over the rule for rows with End date in the past.


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • PKane
    PKane ✭✭✭✭

    Jeff - you are amazing. Thanks again for your help and the opportunity to learn! It worked perfectly -


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @PKane

    Glad I could help. Have a great day.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!