RYGB-Blank Date Condition
Hi,
Below is the formula I am using that works fine. I just wanted to add one more condition and I am not sure how to do it. I tried a few different things with no luck. I want to add a condition that if the Due Date is blank then leave the cell blank (don't put a color ball). Right now it defaults to red if the date is blank which I don't want it to do. Any help would be appreciated. thank you!
=IF(OR(Status@row = "Completed", Status@row = 1), "Blue", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row > TODAY(3), "Green", IF([Due Date]@row >= TODAY(), "Yellow"))))
Best Answer
-
It sounds like you may have just been adding in the criteria in the wrong order. Logic formulas read left-to-right, so if there is an instruction that works for the criteria, it will return the first one in the list. The due date being blank is read as "less than TODAY" which therefore will always be Red, unless you put the blank instruction before the Red instruction.
Try this:
=IF(OR(Status@row = "Completed", Status@row = 1), "Blue", IF([Due Date]@row = "", "", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row > TODAY(3), "Green", IF([Due Date]@row >= TODAY(), "Yellow")))))
Answers
-
It sounds like you may have just been adding in the criteria in the wrong order. Logic formulas read left-to-right, so if there is an instruction that works for the criteria, it will return the first one in the list. The due date being blank is read as "less than TODAY" which therefore will always be Red, unless you put the blank instruction before the Red instruction.
Try this:
=IF(OR(Status@row = "Completed", Status@row = 1), "Blue", IF([Due Date]@row = "", "", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row > TODAY(3), "Green", IF([Due Date]@row >= TODAY(), "Yellow")))))
-
@alexis.ray89371 I agree with Genevieve's solution. Making sure that it is read before the other date IF's is the key.
A different option would be to wrap the entire formula in an IF statement so that it only runs if the [Due Date] is a date.
It doesn't really make much difference in this particular case, but I have a few instances where this saves a lot of hassle.
=IF(ISDATE([Due Date]@row), original_formula)
=IF(ISDATE([Due Date]@row), IF(OR(Status@row = "Completed", Status@row = 1), "Blue", IF([Due Date]@row <= TODAY(), "Red", IF([Due Date]@row > TODAY(3), "Green", IF([Due Date]@row >= TODAY(), "Yellow")))))
Just figured I would throw out another option that can possibly be used elsewhere. Primarily if you have to specify the same criteria in a lot of AND statements but need to expand on if that one criteria is not met then do something else complex.
When building out formulas in sections where each section needs the same piece along with different criteria for different outputs, doing it this way can simplify things quite a bit.
-
Annnnd favouriting this post so I can refer back to this excellent explanation in the future.
Thanks!
-
@Genevieve P Happy to help! 🙂
This isn't the best use case for wrapping the whole formula in an additional IF as it doesn't really simplify much, but it does certainly come in handy when you have a lot going on. I guess a good example for this would be
If Start is a date and Status is "Complete", output "A"
If Start is a date and Status is "In Progress", output "B"
If Start is a date and Status is "Not Started", output "C"
If Start is a date and Status is "Not Needed", output "D"
Then another long nested if with different outputs for if start is not a date and the status is one of the above.
Instead of
=IF(AND(ISDATE(Start@row), Status@row = "Complete"), "A", IF(AND(ISDATE(Start@row), Status@row = "In Progress", "B", IF(AND(.....................................), "C", IF(AND(.....................................), "D"))))
You can cut out all of those AND statements by doing your basic nested IF's
=IF(Status@row = "Complete", "A", IF(Status@row = "In Progress", "B", IF(.............., "C", IF(............., "D"))))
Now you can test this smaller portion to make sure each piece is working. Then just drop it in to the output portion of your IF(ISDATE(Start@row) and you have saved yourself the hassle of writing out all of those AND statements.
I have even nested a nested if in both the "value if true" and the "else" portion of an IF statement like this, but it can really open up some great possibilities.
-
Thank you all for your feedback. This was very helpful and multiple solutions you provided worked!
-
Help Article Resources
Categories
Check out the Formula Handbook template!
