Forum Discussion

MelissaHuff's avatar
MelissaHuff
Copper Contributor
May 28, 2024

List validation based on date calculation

Trying to apply a validation to a list based on a calculated date in the first column. If the date in ColumnA is more than 180 days from today, the value of ColumnB needs to be set to No Action Neede...
microsoft lists
SharePoint Online
Rob_Elliott's avatar
Rob_Elliott
Silver Contributor
May 29, 2024

MelissaHuff you can do this by formatting the Action column in the image below (your Column B) in advanced mode with JSON. It checks the Arrival column and if the arrival date is more than 180 days in the future then set the text to 'No Action Required' and the background color to green. Otherwise (i.e it is less than 180 days ahead) then set the text to 'Action Required' and the background color to red. 

 

 

The JSON for that example is:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if([$Arrival] > addDays(@now(), 180), 'No Action Required', 'Action Required')",
  "style": {
    "background-color": "=if([$Arrival] > addDays(@now(), 180), 'green', 'red')",
    "color": "white",
    "padding-left": "10px"
  }
}

 

And this is the result:

 

 

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

 

Resources