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

Microsoft Lists
SharePoint Online
  • Rob_Elliott's avatar
    Rob_Elliott
    Bronze 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