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.