Help with IF, AND, THEN Formulas to change status column

I am stuck! I've tried multiple variations of this formula but can't seem to get anything to work. I'm looking to have the status column changed automatically based on two other columns; "Date Required" column which is a date and a "No Follow Up Required" column which is a checkbox.

Here's what I'm looking for the formula to do:

If the "No Follow Up Required" checkbox is checked change status to "Complete"

If the "No Follow Up Required" checkbox is unchecked and the "Date Required" date has passed then change status to "Overdue"

Otherwise I want the status to be set to "In Progress"

Here is the formula I've been trying

=IF([No Follow Up Required]@row = 1, "Complete", IF(AND([No Follow Up Required]@row = 0, [Date Required]@row<=TODAY, "Overdue", "In Progress"))

Hoping someone can help me figure this out.

Find more posts tagged with

Accepted answers

ShelbyWarren

@Jay F Try this:

=IF([No Follow Up Required]@row = 1, "Complete", IF(AND([No Follow Up Required]@row = 0, [Date Required]@row < TODAY()), "Overdue", "In Progress"

All comments

ShelbyWarren

@Jay F Try this:

=IF([No Follow Up Required]@row = 1, "Complete", IF(AND([No Follow Up Required]@row = 0, [Date Required]@row < TODAY()), "Overdue", "In Progress"

JayF

@ShelbyWarren You are a freakin lifesaver, it worked!! This was driving me mad haha!

ShelbyWarren

@Jay F Glad it worked. I've been there and done that before too.... 😅

ShelbyWarren

@Jay F Just know that anytime all of the cells mentioned in the formula are blank, the status will be "Overdue". I would add an IF statement that takes into account that if none of the cells contain data, show the status as "Not Started" or something like that.

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.