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

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!

Find more posts tagged with

Accepted answers

Jeff Reisman

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

All comments

Jeff Reisman

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

Emily Kniep

It worked!!! Thank you so much! I figured it was something little but couldn't figure it out. I also used your suggestion of the OR statement.

Thanks again!

Emily

Jeff Reisman

https://community.smartsheet.com/discussion/comment/323833#Comment_323833

Glad it worked for you!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.