Nested and/or formula

JHone
JHone
edited 11/03/21 in Formulas and Functions

I am trying to set an at risk flag when a date is in the past and the status is not cancelled or complete. I have used the following formula in Excel and it works, however, I cannot make it work in Smartsheet. Any advice?

=IF(AND([Due Date]@row < TODAY(), OR(Status@row="Cancelled", Status@row="Complete")), 0, 1))


I did get the following formula to work for just one variable for the status, but I am still unable to get it to work when I try to make it flag if the date is in the past and if the status is anything other than Complete or Cancelled.

=IF(AND([Due Date]@row < TODAY(), Status@row<>"Complete"), 1, 0)

Best Answer

  • JHone
    JHone
    Answer ✓

    That leaves one more closing parenthesis than there are opening parentheses. I figured out that I can get the result I want with the following formula since the status field is a drop down list and is limited to the selections in the drop down list. It is a longer formula and wouldn't work if there was not a limited selection for the status field, but meets my needs.

    =IF(AND([Due Date]@row < TODAY(), OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = "On Hold", Status@row = "")), 1, 0)

Answers

  • The below says, If the actual ship date is blank, the cell equals "in-progress", or if the actual ship date is before today, the cell equals "complete".

    =IF(ISBLANK([Actual Ship Date]@row), "in-progress", IF([Actual Ship Date]@row < TODAY(), "complete", "in-progress"))


    You can also set this to be within 10 days of TODAY to flip a flag risk.

  • Hi @JHone

    It looks like you're just missing a closing parentheses in the right place. You need to close off the AND before stating the 0

    Try this:

    =IF(AND([Due Date]@row < TODAY(), OR(Status@row = "Cancelled", Status@row = "Complete"))), 0, 1)

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • JHone
    JHone
    Answer ✓

    That leaves one more closing parenthesis than there are opening parentheses. I figured out that I can get the result I want with the following formula since the status field is a drop down list and is limited to the selections in the drop down list. It is a longer formula and wouldn't work if there was not a limited selection for the status field, but meets my needs.

    =IF(AND([Due Date]@row < TODAY(), OR(Status@row = "Not Started", Status@row = "In Progress", Status@row = "On Hold", Status@row = "")), 1, 0)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!