List validation based on date calculation

Copper Contributor

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 Needed. I know the validation has to be applied to the list rather than the column. I am using the following syntax but it keeps telling me it doesn't recognize the syntax. I have tried the following statements:

 

=if ([ColumnA]>getFutureTime(180,'Day'), [ColumnB]="No Action Needed")

 

=if ([ColumnA]>(DAY(TODAY)+180), [ColumnB]="No Action Needed", "")

 

TIA

1 Reply

@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. 

 

1-SP.png

 

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:

 

0-SP.png

 

 

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