Sharepoint JSON formula for checking if field is 'on or before' another field or 'after' the other field and then colour accordingly

Question

I am formatting a column, but am struggling to achieve the following

 If [Plan Completed] is 'on or before' [Plan Due Date], then colour Green, or if [Plan Completed] is 'after' [Plan Due Date], then colour Red.

The [Plan Due Date] is a calculated field based off another field, =[Date Detected]+3

The [Plan Completed] is a user entry field.

 

Can't seem to get the conditional formatting to work consistently.

 

JSON Formula being used:

{

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

"elmType": "div",

"style": {

"box-sizing": "border-box",

"padding": "0 2px",

"overflow": "hidden",

"text-overflow": "ellipsis"

},

"attributes": {

"class": {

"operator": ":",

"operands": [

{

"operator": "<=",

"operands": [

{

"operator": "Date()",

"operands": [

{

"operator": "toDateString()",

"operands": [

{

"operator": "Date()",

"operands": [

"[$Plan_x0020_Completed]"

]

}

]

}

]

},

{

"operator": "Date()",

"operands": [

{

"operator": "toDateString()",

"operands": [

{

"operator": "Date()",

"operands": [

"[$Plan_x0020_Due_x0020_Date]"

]

}

]

}

]

}

]

},

"sp-css-backgroundColor-BgCyan sp-css-borderColor-CyanFont sp-field-fontSizeSmall sp-css-color-CyanFont",

{

"operator": ":",

"operands": [

{

"operator": ">",

"operands": [

{

"operator": "Date()",

"operands": [

{

"operator": "toDateString()",

"operands": [

{

"operator": "Date()",

"operands": [

"[$Plan_x0020_Completed]"

]

}

]

}

]

},

{

"operator": "Date()",

"operands": [

{

"operator": "toDateString()",

"operands": [

{

"operator": "Date()",

"operands": [

"[$Plan_x0020_Due_x0020_Date]"

]

}

]

}

]

}

]

},

"sp-css-backgroundColor-BgPeach sp-css-borderColor-PeachFont sp-field-fontSizeSmall sp-css-color-PeachFont",

""

]

}

]

}

},

"txtContent": "[$Plan_x0020_Completed.displayValue]"

}

Answer 1

Hi @Spike_naylor,

Do you want to achieve [Plan Completion] "on or before" [Plan Due Date], color it green, or if [Plan Completion] is "after [Plan Due Date]", color it red?

Here is a test for your reference:

Plan Due Date:

Date Detected:

Plan Completed

Here is Code

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "box-sizing": "border-box",
    "padding": "0 2px",
    "overflow": "hidden",
    "text-overflow": "ellipsis"
  },
  "attributes": {
    "class": {
      "operator": ":",
      "operands": [
        {
          "operator": "<=",
          "operands": [
            {
              "operator": "Date()",
              "operands": [
                {
                  "operator": "toDateString()",
                  "operands": [
                    "@currentField"
                  ]
                }
              ]
            },
            {
              "operator": "Date()",
              "operands": [
                {
                  "operator": "toDateString()",
                  "operands": [
                    "[$Plan_x0020_Due_x0020_Date]"
                  ]
                }
              ]
            }
          ]
        },
        "sp-css-backgroundColor-BgGreen sp-css-borderColor-GreenFont sp-field-fontSizeSmall sp-css-color-GreenFont",
        {
          "operator": ":",
          "operands": [
            {
              "operator": ">",
              "operands": [
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "toDateString()",
                      "operands": [
                        "@currentField"
                      ]
                    }
                  ]
                },
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "toDateString()",
                      "operands": [
                        "[$Plan_x0020_Due_x0020_Date]"
                      ]
                    }
                  ]
                }
              ]
            },
            "sp-css-backgroundColor-BgRed sp-css-borderColor-RedFont sp-field-fontSizeSmall sp-css-color-RedFont",
            ""
          ]
        }
      ]
    }
  },
  "txtContent": "@currentField.displayValue"
}

Here is test result:

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards

Cheng Feng