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. You can get the internal name of your column by following this article: How to find the Internal name of columns in SharePoint Online?

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, 2022 at 7:20
  • You're welcome @Helen. Glad it helped you! Jul 4, 2022 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, 2022 at 7:23

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.