Compare 2 dates and conditionally format

Question

I am trying to compare 2 dates and conditionally format one of the columns based on the result.

I have a 'Due' and 'Date Completed'. So I am trying to format the 'Date Completed' green if on or before 'Due' and red if after 'Due'.

Sorry for my ignorance but I have been unable to find anything to help me. I have worked out that I can't use a calculated field - which is what I was trying to do to start with.

I have tried to change a conditional format code I already have working in another column but this does not work.

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

Many thanks for any help.

Answer 1

If you are using SharePoint online, use this JSON:

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

OR:

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

Note: Make sure you are using correct internal name of "Due" column.

Reference Documentation: Formatting an item when a date column is before or after today's date (advanced)

Answer 2

You can use the below formula.

{
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "txtContent": "@currentField",
   "style": {
      "color": {
         "operator": "?",
         "operands": [
            {
               "operator": "<=",
               "operands": [
                  "@currentField",
                  {
                     "operator": "Date()",
                     "operands": [
                        "[$Due]"
                     ]
                  }
               ]
            },
            "green",
            "red"
         ]
      }
   }
}

Reference: Advanced formatting with Date columns