SharePoint JSON Conditional Formatting: Compare two date columns and ignore blanks

Question

I have a SharePoint Online List that tracks project information. There is a "Start Date" column, a "Due Date" column, and a "Completed Date" column. The "Due Date" column is a calculated column whose formula returns a date value that is 60 days after the date in the "Start Date" column. Here is the "Due Date" column's formula:

=IF(ISBLANK([Completed Date]),"",[Start Date]+60)

I would like cells in the "Completed Date" column that are on or before the date in the "Due Date" column to appear with a green background, and those that didn't in red. I would also like it to ignore cells in the "Completed Date" column that are blank.

I have been successful in getting the colours to work by using code from other forum posts but cannot seem to figure out how to make it ignore null/blank cells. Starting to pull my hair out at how complicated it is to apply simple conditional formatting.

Here is a mock-up of how I'm being asked to make it look.

This is the code I have so far:

{
   "$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 Date]"
                     ]
                  }
               ]
            },
            "green",
            "red"
         ]
      }
   }
}

Thanks in advance for any guidance.

Answer 1

Hi brdmcdgll,

First of all, due to the use of the ISBLANK function, when Completed Date is empty, Due Date is also empty.

So I suggest changing the formula to " =[Start Date]+60 ".

Then in my environment, I used the following JSON Conditional Formatting, which you can use as a reference:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "attributes": {
    "class": "=if(Number([$CompletedDate])==0, '', if([$CompletedDate] <[$StartDate]+5184000000,'sp-field-severity--good','sp-field-severity--blocked'))"
  }
}

---

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.