MIcrosoft List Conditional Formatting - JSON

Question

I'll start by saying that I do not know JSON!

I have a Microsoft List that tracks contracts. I am trying to shade the cells in this column based on date criteria and how far away from the current date it is. The column is a date/time column. I am looking for the following criteria if anyone could help:

The title of the column is "Contract End Date"

• If the current date is more that 6 months prior to the date in the column, the cell should be colored light green.
• If the current date is between 6 months and 3 months before the date in the column the cell should be shaded light yellow.
• If the current date is less then 3 months or greater than the current date, the cell should be shaded light red.

Anyone help me understand how to do this?

Answer 1

You are going to use something like this:

{"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "debugMode": true,
    "txtContent": "@currentField",
    "style": {
        "color": "=if([$Contract_x0020_End_x0020_Date] <= @now - 180 * 24*60*60*1000, '#5cd65c',if(([$Contract_x0020_End_x0020_Date] >= @now - 30 * 24*60*60*1000, #ffe6ee',if(@now - 180 * 24*60*60*1000 > [$Contract_x0020_End_x0020_Date] > @now - 30 * 24*60*60*1000,'#ffff99')))"
  }
}

Here are some good references: https://social.technet.microsoft.com/Forums/office/en-US/c24ec78b-1f25-4d93-94c5-032f0291dc51/conditional-formatting-date-field-with-json-based-on-due-date

https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting#get-started-with-column-formatting

https://spgeeks.devoworx.com/sharepoint-date-column-formatting/

https://www.w3schools.com/colors/colors_picker.asp