Microsoft Lists - conditional formatting column with JSON

Question

Hello,

I have been trying to add conditional formatting to a column within a List in Microsoft Lists, but to achieve what I want I need to use JSON and I'm struggling as I'm unfamiliar with it.

I have a date column named Start which I would like to add a background colour to according to the below conditions:

• Red, if the date is up to 14 days into the future AND if column "Scheduled"? does not equal "Scheduled"

• Orange, if the date is between 15 - 21 days into the future AND if column "Scheduled"? does not equal "Scheduled"

Could someone please help me with the JSON formatting for this? Below is as far as I've gotten, but this JSON is not working and I don't know why. I'm unsure of the correct syntax for IF / AND statements in JSON and I've struggled to find enough information online to help me.

{

   "$schema":"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

   "elmType":"div",

   "debugMode":true,

   "style":{

      "background-color":"=if(([$Start] <= addDays(@now,14)) && ([$Scheduled?] != 'Scheduled'),'#ffa0a0',''))"

   }

}

The error message shown is:

The style values '=if(([$Start] <= addDays(@now,21)) && ([$Scheduled?] != 'Scheduled'),'#ffa0a0',''))' contains one or more of the following disallowed characters ( : & ; ! .

Also, as far as I can see, conditional formatting is applied to columns in all views in the List. Is it possible to apply conditional formatting to one view only?

Thanks,

Shannon

Answer 1

Dear Shannon,

As the Now date is dynamic and the calculated column may not show the correct Now date, I suggest you create a scheduled flow to run each day to show the correct Now date and help you validate whether the JSON formatting works correctly.

You can create the text column Now, Now+14, Now+15 and Now+21 to update the conditional date and create the flow as follows.

Now: formatDateTime(utcNow(),'M/dd/yyyy')

Now+14: formatDateTime(adddays(utcNow(),14),'M/dd/yyyy')

Now+15: formatDateTime(adddays(utcNow(),15),'M/dd/yyyy')

Now+21: formatDateTime(adddays(utcNow(),21),'M/dd/yyyy')

You need to use the internal column name in JSON. For example, for the column Scheduled?, you can click on List Settings>select the column and you will see the column URL like https://contoso.sharepoint.com/sites/sitename/\_layouts/15/FldEdit.aspx?List=%7Blist guid%7D&**Field=Scheduled\_x003f\_**.

So the column internal name is Scheduled_x003f_.

You can use the following code as an example to see and let us know the result in your environment.

{

  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

  "elmType": "div",

  "txtContent": "@currentField",

  "style": {

    "background-color": "= if(@currentField <= addDays(Date(@now,toDateString()),14) && [$Scheduled_x003f_] !='Scheduled','red',if(@currentField > =addDays(Date(@now,toDateString()),15) && @currentField <= addDays(Date(@now,toDateString()),21) && [$Scheduled_x003f_] !='Scheduled','orange','green'))"

  }

}

If the column is showed in the view, the column formatting layout will be showed in all views. However, there are some workarounds. For example, you can hide the formatted column from the view or create a new column with the same values as the formatted column and then hide the formatted column from the view.

Your effort and time are greatly appreciated!

Sincerely

Cliff | Microsoft Community Moderator