Status column to automatically update based on the expiry date

Question

I am trying to set-up a SharePoint List and would like the status column to automatically update based on the expiry date. I am looking to have 3 options; Active, Expired, Needs Attention.

Expired (Red) = past the expiry date

Needs attention (Yellow)= 2 months prior to and up to the expiry date

Active (No colour) = Everything else

I do not have access to power flow so I am looking for JSON code.

Thank you in advance!

Answer 1

Hi @Sofie Gregory ,

Welcome to Q&A forum!

To set up a SharePoint List with a status column that automatically updates based on the expiry date, you can use JSON column formatting. Below is an example of how you can achieve the three status options (Active, Expired, Needs Attention) using JSON code.

{
 "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "=if([$expirydate] < @now, 'Expired', if([$expirydate] > @now + 5184000000, 'Active', 'Needs attention'))",
"style": {
"background-color": "=if([$expirydate] < @now, 'red', if([$expirydate] > @now + 5184000000, ' ', 'yellow'))"
 }
}

And the result:

Good day!

---

If the answer is helpful, please click "Accept as 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.