How can i change the color of date column in sharepoint

Question

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!

Comments

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

Answer 1

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