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!

Tags:

Best Answer

  • Devin Lee
    Devin Lee ✭✭✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!