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?

Answers

  • Bassam Khalil
    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"),"")

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • 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
    Leibel S ✭✭✭✭✭✭

    @Antoine Simmons

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!