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?

1 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




Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.