How do I get a cell to change color based at certain increments based off of a date in a column?
![PKane](https://us.v-cdn.net/6031209/uploads/userpics/SFX5FYMHELX9/n5U477T7XDGVD.jpg)
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
-
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
-
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!
-
Jeff - you are amazing. Thanks again for your help and the opportunity to learn! It worked perfectly -
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!