# IF AND OR Formula

✭✭

What is the easiest way to set up a formula using the IF AND OR functions? I want my flags to turn red IF a Status is not marked Complete OR In Progress AND the Due Date is TODAY or later. This is what I currently have (it's incorrect):

=IF(AND(Status@row <> "Complete", OR(Status@row <> "In Progress", [End Date]@row <= TODAY()), 1, 0))

• ✭✭✭✭✭✭

Try this:

=IF(AND(OR(Status@row <> "Complete", Status@row <> "In Progress"), [End Date]@row <= TODAY()), 1, 0)

• ✭✭✭✭✭

`IF( AND([End Date]@row<=TODAY(), Status@row<>"In Progress", Status@row<>"Complete"),1 ,0)`

The OR() function is not needed.

You have three conditions for when the row should be red:

• [End Date] <= TODAY() and
• [Status] is not "In Progress" and
• [Status] is not "Complete"

The condition for your IF() statement would be expressed as:

`AND([End Date]@row<=TODAY(), Status@row<>"In Progress", Status@row<>"Complete")`

You will need to include NOT() if you use OR() since [Status] could be "In Progress" or "Complete". The expression for this would be:

`NOT(OR(Status@row="In Progress", Status@row="Complete"))`

The condition for your IF() statement would be changed to:

`AND([End Date]@row<=TODAY(), NOT(OR(Status@row="In Progress", Status@row="Complete")))`

The revised IF() statement would be:

`IF( AND([End Date]@row<=TODAY(), NOT(OR(Status@row="In Progress", Status@row="Complete"))), 1, 0)`

• ✭✭✭✭✭✭

Try this:

=IF(AND(OR(Status@row <> "Complete", Status@row <> "In Progress"), [End Date]@row <= TODAY()), 1, 0)

• ✭✭✭✭✭

`IF( AND([End Date]@row<=TODAY(), Status@row<>"In Progress", Status@row<>"Complete"),1 ,0)`

The OR() function is not needed.

You have three conditions for when the row should be red:

• [End Date] <= TODAY() and
• [Status] is not "In Progress" and
• [Status] is not "Complete"

The condition for your IF() statement would be expressed as:

`AND([End Date]@row<=TODAY(), Status@row<>"In Progress", Status@row<>"Complete")`

You will need to include NOT() if you use OR() since [Status] could be "In Progress" or "Complete". The expression for this would be:

`NOT(OR(Status@row="In Progress", Status@row="Complete"))`

The condition for your IF() statement would be changed to:

`AND([End Date]@row<=TODAY(), NOT(OR(Status@row="In Progress", Status@row="Complete")))`

The revised IF() statement would be:

`IF( AND([End Date]@row<=TODAY(), NOT(OR(Status@row="In Progress", Status@row="Complete"))), 1, 0)`

• ✭✭

Thank you both for responding - You're AMAZING! These both worked in the two different capacities that I needed them to work.

• ✭✭✭✭✭✭

Great news!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!