Updating a Status Column Based on criteria in 2 different columns IF AND formula

Emily Kniep
Emily Kniep ✭✭
edited 03/24/22 in Formulas and Functions

HI, I'm trying to auto populate a column if a task is past due based on due date and a status column.

This is the formula I've tried but it comes back as UNPARSEABLE. This one I found on the community

=IF(AND([Due Date]@row < TODAY(), Status@row = "Not Started"), "Past Due"),IF(AND([Due Date]@row<TODAY(), Status@row="In Progress"), "Past Due"))

The goal is to have it return Past due if we are past the due date and the status is either not started or in progress. In my status column my options are Not started, in progress, completed, on hold, no action needed. Otherwise I'd be able to just use not complete and the due date....

Thanks for any help!

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @Emily Kniep

    In your formula, you have an extraneous end parentheses:

    You can also make your formula a little shorter and more efficient by embedding an OR statement inside your AND:

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

    English: IF the Due Date is before today, AND the Status is either "Not Started" OR "In Progress", set this cell value to "Past Due."


    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!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!