Conditional formatting based off of blank date cell

bridgets

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!

Devin Lee

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", ""))))

Devin Lee

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", ""))))

bridgets

Thank you so much @Devin Lee !! That fixed it and your explanation of why makes sense as well. Thanks again