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

PKane

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.

Jeff Reisman

@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.

Jeff Reisman

@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.

PKane

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

Jeff Reisman

@PKane

Glad I could help. Have a great day.