IF/AND/OR Statements for date and status column

Options

Hi SS Community!

Very new to SS and clearly am not well versed in formulas. I need help turning the At Risk flag red when the Date Needed By is past due and the Status is Open or In Progress.

Here is the best I could do...

=IF(TODAY() >[Date Needed By]@row, IF(AND(Status@row = "Open", IF(OR(Status @row = "In-Progress")), 1,0))

Thanks in advance!

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Options

    =IF(AND(DateNeeded@row < TODAY(), OR(Status@row = "open", Status@row = "in progress")), "Red")

    The way I do this is I set up three columns.

    Column 1 tests if Date Needed is past:

    =DateNeeded@row < TODAY()

    Column 2 test is the Status column:

    =OR(Status@row = "open", Status@row = "in progress")

    I make sure those get me the result I need independently.

    Then I smoosh them together with an AND formula.

    Then I throw the IF around it.

  • Jodi Vondran
    Options

    Thanks for the fast reply!


    I got =IF([Date Needed By]@row > TODAY(), 0, 1) to work!

    Now for the second part. Here is what I tried...I'm getting #UNPARSEABLE

    =IF(OR([Status@row = "Open", Status@row = "In-Progress", 0, 1))

    Thanks in advance!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!