calculated column: How to add more variables to a "If-Then" formula SharePoint 2013

Question

Document Library Calculated Column in SharePoint 2013 Enterprise version:

I want the column "Priority" to reflect a calculated status based on data in "Current Status" as follows:

If column "Expire Date" is less than 180 days from today, and "Current Status" is "Active", or "Expired", then Priority is "High",

Otherwise

...... and "current status" is "Renewed", then Priority is "No Action Required"

Or else (meaning the expiration date is no less than 180 days), "Normal"

The formula I am trying:

=IF(AND([Expiration Date]<[TODAY]+180, [Current Status]="ACTIVE" OR "EXPIRED", "HIGH", IF(AND([Expiration Date]<[TODAY]+180, [Current Status]="RENEWED", "NO ACTION REQUIRED", "NORMAL"))

However, I get an error in return. Any help?

Answer 1

The OR is for boolean values (2 conditions) not for multiple possibilities for the = sign. This should work:

=IF(AND([Expiration Date]<[TODAY]+180, OR([Current Status]="ACTIVE",[Current Status] = "EXPIRED")), "HIGH", IF(AND([Expiration Date]<[TODAY]+180, [Current Status]="RENEWED"), "NO ACTION REQUIRED", "NORMAL"))

You were also missing several parenthesis around the AND formulas.

Reference: https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx