Formula Help for IF/And/Or

Options

I'm trying to write a formula which sets the At Risk Flag.

If the Status="At Risk" the flag should be set

Or

If the End Date is Greater than Today AND the Status is not equal to "Complete" the Flag should be set.

Otherwise, the flag should not be set.


Here's what I came up with. It works correctly when the date is after today and the status is not equal to complete, but the flag does not set if the status is changed to At Risk.

=IF(AND([End Date]@row < TODAY(), NOT(Status@row = "Complete")), OR(Status@row = "At Risk", 1, 0))

Any help is appreciated!

Ed

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @Ed S

    This is a common error when combining ORs and ANDs. You need your AND statement to be one of the options inside the OR statement:

    =IF(OR(Status@row = "At Risk", AND([End Date]@row < TODAY(), NOT(Status@row = "Complete")), 1, 0)

    In English: If the status is "At Risk," OR if both the end date is before today AND the status is not "Complete," set the flag, otherwise do not set the flag.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Ed S
    Options

    Hi Jeff,

    Thanks for the reply. I copied the formula and am getting the message #incorrect argument set. Any idea's what might cause that?

    Thanks,

    Ed

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 08/09/22
    Options

    *Edited because I missed an end parentheses.

    It always helps to share a screenshot of your formula as it appears while editing it in Smartsheet, with the color-coding.

    But let's try this first, and if this doesn't work, share the screenshot:

    =IF(OR(Status@row = "At Risk", AND([End Date]@row < TODAY(), Status@row <> "Complete")), 1, 0)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Ed S
    Options

    That worked PERFECTLY! Thank you very much.

    Ed

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!