Need to ignore text (NA) in cell referenced in a formula

I have the following formula to determine Status:

=IF(OR(TODAY() > [Due Date1]@row, TODAY() > [ Due Date2]@row, TODAY() > [Due Date3]@row, TODAY() > [Due Date4]@row), "Red", "Green")

It works as expected when dates are entered into the referenced Due Date cells. However, if text (NA) is entered, I get a #INVALID OPERATION error.

Is there an operation to ignore NA if it is in ANY of the Due Date cells?

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

Hi @Antoine Simmons

hope you are fine, try the following formula:

=IFERROR(IF(OR(TODAY() > [Due Date1]@row, TODAY() > [ Due Date2]@row, TODAY() > [Due Date3]@row, TODAY() > [Due Date4]@row), "Red", "Green"),"")

Antoine Simmons

Hi @Bassam.M Khalil. I appreciate you taking the time to answer. This solution you provided does remove the #INVALID OPERATION error, but due to how I am using the sheet, it will not work. The "Red" "Green" response in the IF OR formula is meant to be a visual flag if anything on that line is past the due date. Each line has 4 due dates with each associated with a different assignment. Using IFERROR just returns an empty response, negating the usefulness of the "Red" "Green" indicators. I have since found a different work around.

Leibel S

@Antoine Simmons

You would need to wrap the IFERROR around each of your date references within the formula

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.