Conditional Formatting based on Date
All,
I would like to create a conditional formatting based on a date.
Use case below:
Column A is a date - I would like Column B to turn yellow if it is under 10 days from Column A date, turn orange if past 15 days and turn red if past 15 days.
Is this possible? Would I need to create a formula? Doesn't look like I can base this solely on conditional formatting.
Best Answer
-
Hi @NBudd
You could write a formula to do something similar if you wanted to have status balls change depending on the date (this may help if you wanted to pull a Report based on the colours), but it's also possible to do just with conditional formatting.
The order in which you write your conditional formatting rules matters, so first start with the Yellow, then Orange, then Red. Here's a picture of how I set up 3 rules to colour the background of Column B:
You'll see that I put "If Date is in the last 15 days" before the red rule, because then the Red one will only happen if the date is older than the last 15 days. This is where you can find the date rules:
Let me know if you have any questions about this! You can read more about Conditional Formatting in our Help Center (see here).
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi @NBudd
You could write a formula to do something similar if you wanted to have status balls change depending on the date (this may help if you wanted to pull a Report based on the colours), but it's also possible to do just with conditional formatting.
The order in which you write your conditional formatting rules matters, so first start with the Yellow, then Orange, then Red. Here's a picture of how I set up 3 rules to colour the background of Column B:
You'll see that I put "If Date is in the last 15 days" before the red rule, because then the Red one will only happen if the date is older than the last 15 days. This is where you can find the date rules:
Let me know if you have any questions about this! You can read more about Conditional Formatting in our Help Center (see here).
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you so much!!!
-
No problem! Glad this worked for you 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hello. I have the same question but was not able to apply format based on column A to column B. How do I compare the two dates?
-
Hi @beatea
What column type is Column A? Could you provide a screen capture of your sheet (but block out sensitive data) and identify what you want the conditional formatting to do?
Thanks,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!