3

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.

2 Answers 2

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)

2
  • 1
    thank you so much :-) - so very much appreciated.
    – Helen
    Jul 4 at 7:20
  • You're welcome @Helen. Glad it helped you! Jul 4 at 7:42
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

1
  • 1
    Thank you :-) this also worked for me - very much appreciated.
    – Helen
    Jul 4 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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