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
-
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
-
Try adding that IF to the beginning...
=IF([Due Date]44 = "", "", 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", "")))))
-
Unfortunately no. While I do get the empty cell for the RYG now it only returns a Green for any date and it won't turn to Grey when there is a Date Completed.
-
Sorry about that. When I copy/pasted from your post, I didn't realize you were using different row numbers between the two formulas.
The best way to avoid something like that happening is the replace the row number with an @row reference. This simply tells the formula to look at the specified column on whichever row the formula is on.
=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", "")))))
-
That worked and I actually realized that I had the row numbers in there after I sent it to you and had updated my sheet to fix all that with @row. I checked it with the new formula above and that worked. Sure...I tried my new IF 2nd and Last in the string and didn't think to try it in 1st place; makes sense!
-
Glad it is working now! 👍️
-
@Paul Newcome is the path in a formula to put up front the criteria that says, check item X and if it does Y then do not proceed (in my example, if no date, don't return anything); after that then what is the next item in terms of not proceeding (ex, if there is a date complete, return Gray); then proceed with those other criteria that you want it to return (if it's this then return Green; then Yellow; then Red).
Trying to learn the best practice for creating formulas and how they should cascade.
Pam
-
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?
-
Yes, it does make sense! Thanks much!!
-
Great! Happy to help! 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!
