Conditional formatting based off of blank date cell
Hello! I have tried to solve this problem with a lot of experimentation and reading many posts in the Smartsheet Community. Unfortunately, I just can't get it to work.
I am trying to change the color of the circle symbol of a status field based off of the value in a date field (Target Date). I want the symbol to turn gray if the Target Date is blank. I have tried this based off of evaluating the date field itself as well as eventually creating a helper column. The helper column populates a "1" if the Target Date is blank. Then, I am referencing that helper column in my Status formula. still can't get it to work. No matter what I try, if the Target Date is blank, it just keeps updating the Status color to Red.
Here is my most recent version of the formula, attempting to utilize the helper column:
=IF([Target Date]@row < TODAY(-7), "Red", IF(AND([Target Date]@row >= TODAY(-7), [Target Date]@row <= TODAY(7)), "Yellow", IF([Target Date]@row > TODAY(7), "Green", IF([Helper - Target Date Blank]@row = "1", "Gray"))))
Any ideas would be much appreciated!
Best Answer
-
Hey @bridgets
You want to check to see if it's blank first otherwise the blank will trigger the Red. Try this formula
=IF(ISBLANK([Target Date]@row), "Gray", IF([Target Date]@row < TODAY(-7), "Red", IF(AND([Target Date]@row >= TODAY(-7), [Target Date]@row <= TODAY(7)), "Yellow", IF([Target Date]@row > TODAY(7), "Green", ""))))
Answers
-
Hey @bridgets
You want to check to see if it's blank first otherwise the blank will trigger the Red. Try this formula
=IF(ISBLANK([Target Date]@row), "Gray", IF([Target Date]@row < TODAY(-7), "Red", IF(AND([Target Date]@row >= TODAY(-7), [Target Date]@row <= TODAY(7)), "Yellow", IF([Target Date]@row > TODAY(7), "Green", ""))))
-
Thank you so much @Devin Lee !! That fixed it and your explanation of why makes sense as well. Thanks again
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!