Forum Discussion

LtMatt's avatar
LtMatt
Copper Contributor
Apr 27, 2023

Date Validation, Renewal Date

One choice style column, "Approved," lists whether the person is approved. Another column titled "Renewal Date" identifies the date they need to renew.

I want the "Approved" column to change to "No" when the date in the "Renewal Date" has passed.

Is there a formula that would help with this?

App
Microsoft Lists
SharePoint Online
SharePoint Server
  • RobElliott's avatar
    RobElliott
    Silver Contributor
    Apr 28, 2023

    LtMatt you can't do this with a formula in a calculated column because it just stores static data and the column wouldn't be re-checked until you edited the item. But you can do this with column formatting in advanced mode using the following JSON (changing the column names to yours):

    {
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "=if(@now > [$Renewal],  'No', 'Yes')",
  "style": {
    "background-color": "=if(@now > [$Renewal], '#5e0404', '#004444')",
	"color": "white"
  }
}

     

     

    Rob
    Los Gallardos
    Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

Share