SharePoint List JSON Conditional Format of Date Column When # days passed

Question

I've went through a bunch of questions trying to find the answer to my questions, but a lot of recommendations don't quite fit into the code that I have. I'm using the formatting that SharePoint provides, which is different than the code that everyone else is posting. Will someone either convert the code I post to show me how to change it to the user recommended code or convert what i have to meet my requirements?

Basically, I have a column with a list of selections (Reinvestigation) and another with the date they record when that action was completed (Reinvestigation Key Date). When a specific option is selected in Reinvestigation (Awtg NBIS Completion), I want the date to highlight red after 12 days have passed. When using the design mode, it only allows current date or a specific date. I tried entering addDays(@now,12) where @now is posted in the code, but it doesn't register.

{
  "$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": "==",
              "operands": [
                "[$Reinvestigation]",
                "Awtg NBIS Completion"
              ]
            },
            {
              "operator": "<=",
              "operands": [
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "toDateString()",
                      "operands": [
                        {
                          "operator": "Date()",
                          "operands": [
                            "[$Reinvestigation_x0020_Key_x0020_]"
                          ]
                        }
                      ]
                    }
                  ]
                },
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "toDateString()",
                      "operands": [
                        {
                          "operator": "Date()",
                          "operands": [
                            "@now"
                          ]
                        }
                      ]
                    }
                  ]
                }
              ]
            }
          ]
        },
        "sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-field-fontSizeSmall sp-css-color-CoralFont",
        ""
      ]
    }
  },
  "txtContent": "[$Reinvestigation_x0020_Key_x0020_.displayValue]"
}

Answer 1

Try using SharePoint column formatting JSON like below for your date & time column:

{
  "$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": "=if(Number([$Reinvestigation_x0020_Key_x0020_]) == 0, '', if([$Reinvestigation]=='Awtg NBIS Completion' && [$Reinvestigation_x0020_Key_x0020_] < addDays(@now, -12), 'sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-field-fontSizeSmall sp-css-color-CoralFont',''))"
  },
  "txtContent": "[$Reinvestigation_x0020_Key_x0020_.displayValue]"
}

Where Reinvestigation and Reinvestigation_x0020_Key_x0020_ are internal names of your list columns. You can get the internal name of your SharePoint list columns by following this article: How to find the Internal name of columns in SharePoint Online?

Here I have used Number([$Reinvestigation_x0020_Key_x0020_]) == 0 to check if SharePoint date column is blank or not. You can find other ways to check if SharePoint date & time column is empty or not at: SharePoint JSON formatting: Check if date & time column is blank/empty

Answer 2

I think the comparison for dates and @now uses ticks. So you would need to compare @now + (12 * 86400000) for your twelve days.

I haven't used the way you are formatting it, but I have a date column I need to color based on if it is 60, 45, 30, 15, or less than 0 (in the past) days away. Here is the json I used for that. It uses the built-in classes, but you could change this around to just apply a specific style color.

"attributes": {
  "class":"=if([$ReviewDate] < @now,
              'sp-field-severity--blocked',
              if([$ReviewDate]  <= @now + (15 * 86400000), 
                'sp-field-severity--severeWarning less15',
                 if([$cReviewDate]  <= @now + (30 * 86400000),
                    'sp-field-severity--warning less30',
                    if([$ReviewDate]  <= @now + (45 * 86400000),
                       'sp-field-severity--good less45',
                       if([$ReviewDate]  <= @now + (60 * 86400000),
                          'sp-field-severity--good50 less60',
                          if([$ReviewDate]  <= @now + (74 * 86400000),
                            'sp-field-severity--good30 less74',''
                          )
                       )
                    )
                 )
              )
           )"

}