Return null if there is no date

Easy question: in the formula below, I need to add a final (or starting?) condition that if there is NO date in the Due Date column that no colored ball will appear. Right now, if a task is added and they haven't put in date it is showing Red and that is throwing the metrics off (as the metrics are looking for the Red ball).

=IF([Task Complete]41 = 1, "Gray", IF([Due Date]41 >= TODAY(+15), "Green", IF(AND([Due Date]41 > TODAY(+6), [Due Date]41 < TODAY(+15)), "Yellow", IF([Due Date]41 <= TODAY(+7), "Red", ""))))

I actually thought that the final "" after the word Red would have caused this to happen (no ball) but it still just returns a Red if that Due Date is left blank.

I tried adding: IF([Due Date]44 = "", "") but it didn't work even though it does work on it's own.

Easy fix?

Best Answer

  • Paul Newcome
    Paul Newcome Community Champion
    Answer ✓

    The best way I can explain it is that nested IF's work from left to right and stop at the first true value. That means everything that is passes up is implied to be false.

    Using this formula...

    =IF([Due Date]@row = "", "", IF([Task Complete]@row = 1, "Gray", IF([Due Date]@row >= TODAY(+15), "Green", IF(AND([Due Date]@row > TODAY(+6), [Due Date]@row < TODAY(+15)), "Yellow", IF([Due Date]@row <= TODAY(+7), "Red", "")))))


    If the Due Date is blank, then it will return a blank. If the Due Date is not blank, then it will move on to evaluate the Task Complete Column. The reason we don't have to say the Task Complete column = 1 AND the Due Date is not blank, is because it is already implied simply by making it to the second step.


    Using this logic, we can actually rearrange the above slightly to remove the AND function from the "Yellow" argument.

    Right now you have

    If the Due Date is greater than 6 and less than 15, "Yellow". Then you have If the Due Date is less than 7, "Red".

    If we put the "Red" argument first, being greater than 7 will already be implied when it evaluates for the "Yellow" argument.

    =IF([Due Date]@row = "", "", IF([Task Complete]@row = 1, "Gray", IF([Due Date]@row >= TODAY(+15), "Green", IF([Due Date]@row <= TODAY(+7), "Red", IF([Due Date]@row < TODAY(+15), "Yellow", "")))))


    Does that make sense?

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!