Make Column Set to Expire if End Time has Passed

[BS1002215]

Hi,

I have a SharePoint list which is being used as a datasource for a powerapp. I have created a column called expired and I have added an If statement below which checks if the end time has passed, this works but it doesn't update automatically? It only works on new items that are submitted. I have also noticed it only changes to expires if the time is 7 hours behind the actual real time, is it possible to convert this to GMT Standard Time?

=IF([End Time]>NOW(),"Pending","Expired")

Thanks in advance.

[#2]

@BS1002215 a calculated column will not update dynamically, it only updates when an item is created or modified of if the formula is changed. But you can do this with some simple JSON column formatting.

In the image below the items were created on 7th September and the formula in both the ArrivalStatus and ArrivalStatusJSON columns is =IF(Arrival>NOW(),"Pending","Expired"). But you'll see that as today is now the 8th September the ArrivalStatus column for the arrivals on 8th September has not updated.  However the ArrivalStatusJSON column has:

The JSON for the ArrivalStatusJSON column  is:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if([$Arrival] > @now ,'Pending', 'Expired')",
  "style": {
    "background-color": "=if([$Arrival] > @now ,'green', 'gold')",
    "color": "=if([$Arrival] > @now ,'white', '')",
    "padding-left": "4px"
  }
}

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)