Nested IF Health Status Needs

Erica Shaeffer

I'm working on a formula to give me a health symbol based on today's relation to the due date and factoring in whether the task has already been completed (based on the presence or lack of completed date). I've messed with it a few times, but it's giving me an #INCORRECT ARGUMENT SET error.

Here is my formula: =IF(AND([Due Date]@row > TODAY(+7), NOT(ISBLANK([Completed Date]@row))), "Green", IF(AND([Due Date]@row < TODAY(), ISBLANK([Completed Date]@row)), "Red"), IF(AND([Due Date]@row <= TODAY(+7), ISBLANK([Completed Date]@row)), "Yellow"))

I'm sure I'm missing something very simple. Please let me know if additional info is needed to assist. Thanks!

Paul Newcome

Try this:

=IF([Completed Date]@row <> "", "Green", IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow")))

Erica Shaeffer

@Paul Newcome That removed the argument error message, but didn't give health statuses the way I need. I currently have no complete dates, because this event is in the future, and the formula you suggested only gave health status to rows that had no due date and left the others blank. Do you have any further suggestions? See screenshot attached.

screenshot-app.smartsheet.com-2022.06.27-13_05_17.png

Paul Newcome

My apologies. I was just cleaning up your formula a bit and used the same logic.

Complete Date filled in = Green

Due Date in the past = Red

Due Date in the next 7 days = Yellow

This will leave the RYG blank if there is no due date.

=IF([Due Date]@row <> "", IF([Completed Date]@row <> "", "Green", IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow"))))

This still does not have an output for due dates that are more than 7 days out though.