RYGB-Blank Date Condition



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


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @alexis.ray89371 I agree with Genevieve's solution. Making sure that it is read before the other date IF's is the key.

    @Genevieve P

    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.

  • Genevieve P.
    Genevieve P. Employee Admin

    @Paul Newcome

    Annnnd favouriting this post so I can refer back to this excellent explanation in the future.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @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.

  • alexis.ray89371
    alexis.ray89371 ✭✭✭✭✭

    Thank you all for your feedback. This was very helpful and multiple solutions you provided worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!