IF OR NOT ISBLANK

I am trying to write a column formula to return a value of "Yes" if certain criteria are met but am struggling to understand what I am doing wrong.

I need the column Removed From Respirator Program to change to Yes when

  • Expiration date is not blank; and
  • Expiration date is one day past todays date; or
  • when Do you need to renew your respirator qualification is answered No

Here is my last attempt before reaching out here. I have tried several iterations but nothing has worked for me yet. This halfway works but not the part of Do you need to renew your respirator qualification = No.

=IF(AND(NOT(ISBLANK([Expiration Date]@row)), OR([Do you need to renew your respirator qualification]@row = "No", [Expiration Date]@row <= TODAY(-1))), "Yes", "")

Thanks in advance!

Tags:

Answers

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭

    Hello,

    You can give this a shot and see if it works for you:

    =IF(AND(NOT(ISBLANK([Expiration Date]@row)), OR([Do you need to renew your respirator qualification]@row = "No", [Expiration Date]@row = TODAY() + 1)), "Yes", "")

    This formula checks if:

    1. Expiration Date is not blank
    2. Either "Do you need to renew your respirator qualification" is answered "No"
    3. Or, Expiration Date is one day past today


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!