3

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?

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

  • I tried the formula but still getting error. Let me try it again. – Alfatah Kader Jul 20 '16 at 19:09
  • Here is the error: its telling me I can't use "Today" Sorry, something went wrong Calculated columns cannot contain volatile functions like Today and Me. Technical Details Learn more about the syntax for formulas. Troubleshoot issues with Microsoft SharePoint Foundation. Correlation ID: 82be919d-40eb-2000-381c-78f3c8d00386 Date and Time: 7/20/2016 12:14:47 PM – Alfatah Kader Jul 20 '16 at 19:16
  • UPDATE: I worked around it by creating a COLUMN called TODAY and then inserting default date as today. Thanks for the help. It worked once I added the column above. – Alfatah Kader Jul 20 '16 at 19:22
  • I assumed [TODAY] was a custom column. Unfortunately, calculated columns are calculated only when the item is added/updated. If you want this to be updated based on when it's being viewed then a calculated column will not work for you. You'll need to use views or custom display code like JSLink. – theChrisKent Jul 20 '16 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.