Conditional Formatting based on Dates
Is there a way to have the system highlight using Conditional Formatting when it's past a number of days?
Yellow when it's 30 days after "booked date"
Orange when it is 45 days after "booked date"
Red when it is 60 days and older after "booked date"
Joe Goetschel | Associate Director, Smartsheet
CrossCountry Consulting - Smartsheet Partner
"The only real limitation of Smartsheet is the level of effort required to achieve your goal."
Best Answer
-
Try this...
=IF(TODAY() > [Booked Date]@row + 60, "Red", IF(TODAY() > [Booked Date]@row + 45, "Orange", IF(TODAY() > [Booked Date]@row + 30, "Yellow")))
Then you can set up your conditional formatting to highlight your column(s) based on the text value in this column.
Answers
-
Hi @Joe Goetschel
Hope you are fine, you can create a helper column calculatin how many day past the booked day and create your Conditional Formatting rule using this helper column:
past days = today() - booked date
then you create the Conditional Formatting rule
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
How would the formula read? I can do a DATEONLY to get the correct dates for each level in 3 different columns.
=DATEONLY([Proposal Delivery Date]@row + 30)
=DATEONLY([Proposal Delivery Date]@row + 45)
=DATEONLY([Proposal Delivery Date]@row + 60)
Is there an IF I can do that takes all of these and have it give me 0, 30, 45, 60?
Joe Goetschel | Associate Director, Smartsheet
CrossCountry Consulting - Smartsheet Partner
"The only real limitation of Smartsheet is the level of effort required to achieve your goal."
-
Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Try this...
=IF(TODAY() > [Booked Date]@row + 60, "Red", IF(TODAY() > [Booked Date]@row + 45, "Orange", IF(TODAY() > [Booked Date]@row + 30, "Yellow")))
Then you can set up your conditional formatting to highlight your column(s) based on the text value in this column.
-
Thank you @Paul Newcome that did the trick!
Joe Goetschel | Associate Director, Smartsheet
CrossCountry Consulting - Smartsheet Partner
"The only real limitation of Smartsheet is the level of effort required to achieve your goal."
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives