How to Automatically set Expiration status in a sharepoint list column based on the expiry Date column

Question

I am setting up a SharePoint list to keep a record of certifications done by my team. Some certifications have a Date of Expiry, while some do not. So, in the Date of Expiry column, there are empty cells as well. I want the column "Expiration Status" to automatically detect/change status based on the Date of Expiry column. I want to have three options. expired (Date of Expiry is today or before today), Needs Attention (Date of Expiry is in 1 month), and Valid (Date of Expiry is null or after a month).
I can setup a flow or calculated value or JSON code.

Answer 1

Hi. Just a thought and not sure if it will work or a good idea but maybe you can create a flow to initiate when a new item is created and grab the expiry date and then set an action to wait until that date to perform the action to update the status?

Answer 2

Hi @Tooba Waheed,

Thanks for reaching out to us. We are very pleased to assist you.

The calculate column will meet your needs and it is very simple to implement, so we recommend that you add the following formula to your "Expiration Status" calculate column.

=IF(ISBLANK([Date of Expiry]), "Valid", 
    IF(TEXT([Date of Expiry], "yyyy-MM-dd") <= TEXT(TODAY(), "yyyy-MM-dd"), "Expired", 
        IF([Date of Expiry] <= DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, DAY(TODAY())), "Needs Attention", "Valid")))

Here are my test results:

If you have any questions, please do not hesitate to contact me.

Moreover, if the issue can be fixed successfully, please click "Accept Answer" so that we can better archive the case and the other community members who are suffering the same issue can benefit from it.

Your kind contribution is much appreciated.