How to ignore blank cells in health formula

Laura L

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

Nic Larsen

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"

Nic Larsen

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"

Laura L

Brilliant, thank you so much!