How to ignore blank cells in health formula

Laura L
Laura L ✭✭✭
edited 01/29/24 in Formulas and Functions

I have a working formula for task health based on % Complete and Target Completion Date (see below). I'd like this formula to ignore any rows with blank Target Completion Date and display nothing in the Health column if there is no Target Completion Date. Right now it's defaulting to Red. I know I should add ISBLANK, but I cannot get it to work properly. Any help would be greatly appreciated.

=IF([% Complete]@row = 1, "Gray", IF(AND([% Complete]@row < 1, [Target Completion Date]@row < TODAY()), "Red", IF(AND([% Complete]@row < 0.5, [Days Due]@row < 5), "Yellow", "Green")))



Tags:

Best Answer

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    Try:

    =IF(ISBLANK([Target Completion Date]@row), "", IF([% Complete]@row = 1, "Gray", IF(AND([% Complete]@row < 1, [Target Completion Date]@row < TODAY()), "Red", IF(AND([% Complete]@row < 0.5, [Days Due]@row < 5), "Yellow", "Green"

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!