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

Antoine Simmons

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?

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