How to automate a status column to change based on expiry date of item in a SharePoint Document Library?

Question

Hi,
I am creating a document library for QMS certificates and would like the "Status" column of the items in the document library to automatically change without user input when the item's "Expiry Date" is approaching or passed. How do I go about doing so?

Conditional formatting of the "Status" column will only allow me to select a "Today" or "A specific date" to compare the "expiry date" to. My ask is to have the status change from "Effective" to "Expired" when the expiry date is approaching or passed.

Answer 1

Could you describe the specific conditions of "when the expiry date is approaching or passed"?

For example, select Status column -> Column settings -> Format this column -> Advanced mode -> Add following codes.

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"debugMode": true,
"txtContent": "=if([$ExpiryDate] >= [$EffectiveDate], 'Expired', 'Effective')",
"style": {
"background-color": "=if([$ExpiryDate] >= [$EffectiveDate], 'red', 'green')"
}
}

Result:

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi Emily,

The status folder displays an error message after the code you provided was saved.