Forum Discussion

BS1002215's avatar
BS1002215
Copper Contributor
Sep 01, 2023

Make Column Set to Expire if End Time has Passed

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.

Document Library
SharePoint Online
Sites
  • Rob_Elliott's avatar
    Rob_Elliott
    Bronze Contributor
    Sep 08, 2023

    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)

Resources