IF AND OR Formula

ssummer

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))

Thank you for your help!

KPH

Try this:

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

Toufong Vang

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)

KPH

Try this:

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

Toufong Vang

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)

ssummer

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

KPH

Great news!