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

Jenna Al-Sammarraie 20 Reputation points
2023-01-17T20:25:25.7533333+00:00

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?User's image

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. User's image

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
11,176 questions
0 comments No comments
{count} votes

Accepted answer
  1. Emily Du-MSFT 49,846 Reputation points Microsoft Vendor
    2023-01-18T08:30:29.7533333+00:00

    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:

    User's image


    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Jenna Al-Sammarraie 20 Reputation points
    2023-01-26T21:11:58.39+00:00

    Hi Emily,

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

    User's image

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.