Nested IF Statement Help Needed

Andrea Whittaker

Hi there,

Here is what I am trying to achieve:

If the % Complete column =100%, then return Gray to the status

If a task is overdue (using the End Date column), return Red to the status

If a task is due within 5 days, return Yellow to the status

Is a task is due in 6 or more days, return Green to the status

If there is no date added to the End Date column, then leave the status blank (or a dash would be fine)

Here is the formula I am using: =IF([% Complete]@row = 1, "Gray", IF([End Date]@row < TODAY(), "Red", IF(AND([End Date]@row >= TODAY(), [End Date]@row < TODAY(+5)), "Yellow", IF([End Date]@row > TODAY(+5), "Green", "-"))))

It seems to be working except for that it is returning red to the status if there is no date entered. I also tried added an IF statement for ISBLANK but that didn't work either.

Thanks in advance for your help!

Paul Newcome

That's because blank dates are always considered less than an actual date (to include the TODAY() function). So it is reading the blank date as less than TODAY() which triggers the "Red" status. Try using another IF statement immediately after the [% Complete] one to specify what to do with blanks then continue out the rest of your colors.

Paul Newcome

You forgot to close out the ISBLANK statement after [End Date]@row. Moving one of the closing parenthesis from the end of the formula to close out the ISBLANK statement should work for you.

Paul Newcome

That's because blank dates are always considered less than an actual date (to include the TODAY() function). So it is reading the blank date as less than TODAY() which triggers the "Red" status. Try using another IF statement immediately after the [% Complete] one to specify what to do with blanks then continue out the rest of your colors.

Andrea Whittaker

Thanks for the advice, I'm still running into an issue though.

I tried this: =IF([% Complete]@row = 1, "Gray", IF(ISBLANK([End Date]@row, "", IF([End Date]@row < TODAY(), "Red", IF(AND([End Date]@row >= TODAY(), [End Date]@row < TODAY(+5)), "Yellow", IF([End Date]@row > TODAY(+5), "Green"))))))

And received an #INCORRECTARGUMENTSET message - what am I doing wrong?

Paul Newcome

You forgot to close out the ISBLANK statement after [End Date]@row. Moving one of the closing parenthesis from the end of the formula to close out the ISBLANK statement should work for you.

Andrea Whittaker

That was it! I thought I needed another parentheses, but forgot to remove the one from the bottom when I was testing it. Thank you so much for your help!

Paul Newcome

Happy to help. 👍️