Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
1 like 0 dislike
4k views
in SharePoint Server by 2 3 3
edited by

I have a SharePoint list with a date column, I want to change the column background color using JSON to be:

  • Red > if the Due Date is passed.
  • Yellow > if the Due Date is less than 10 days.
  • Green > if the Due Date is more than 10 days.
  • White > if the Due Date is null / blank / empty.

Please share with me the correct JSON SharePoint column formatting for the date column based on conditions!

by 54 119 241
0 0
What's your SharePoint version? Check supported sharepoint version for JSON column at https://debug.to/472/missing-column-formatting-in-sharepoint-2016

1 Answer

1 like 0 dislike
by 54 119 241
edited by

Column formatting for date column based on condition in SharePoint

  1. First, you should specify the date range!
  2. Second, you should check if the date field is NULL or blank!

Claculate days in JSON Column formatting in SharePoint

if you need to check the field date <= Today + 10 days, so you should use this formula

=if([$DueDate] <= @now + Number of days * 24*60*60*1000,,)

In your case, the number of days equals to (864000000) = 10*24*60*60*1000

=if([$DueDate] <= @now + 864000000,,)

Check if the date field is empty or blank in JSON Column Formatting in SharePoint

You can use Number() function to check if the date field is NULL or Blank

=if( Number([$DueDate])==0,,)

Change the color of date column in SharePoint using JSON Column Formatting

{
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "txtContent": "@currentField",
   "style": {
      "color": "=if([$DueDate] <= @now , 'red', if([$DueDate] <= @now + 864000000, 'yellow', if([$DueDate] >= @now + 864000000 , 'green', if(Number([$DueDate])==0, ''))))"
   }
}

If the current field is the date field, try the below formula

  {
      "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
      "elmType": "div",
      "txtContent": "@currentField",
      "style": {
       "color": "=if(@currentField <= @now , 'red', if(@currentField <= @now + 864000000, 'yellow', if(@currentField >= @now + 864000000 , 'green', if(Number(@currentField)==0, ''))))"
      }
   }

Change the background color of date column in SharePoint using JSON Column Formatting

{
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "txtContent": "@currentField",
   "style": {
      "background-color": "=if(@currentField <= @now , 'red', if(@currentField <= @now + 864000000, 'yellow', if(@currentField >= @now + 864000000 , 'green', if(Number(@currentField)==0, ''))))"
   }
}

Note: JSON Column Formatting option is only available for

  • SharePoint Server 2019.
  • SharePoint Online.
  • SharePoint Online Small Bussiness.

It's strongly recommended to read Date Conditional Formatting In SharePoint 2019

If you don’t ask, the answer is always NO!
...