Using If-Then-Else structure to format rows based on a date column

Question

Hoping someone can help. I have been working on this issue for a few days now (lots of googling and playing with the code formatting).

What I'm trying to accomplish: I have a sharepoint site to track the status of items and am trying to format the rows of data using the $Expiration (date) column. Depending on the date listed in the expiration I would like the rows to come up in different colors so that as the expiration date approaches the colors change to progressively more red.

Below is the code so far... I have tried other versions but none are performing as expected.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "if": {
    "operator": ">",
    "operands": [
      {
        "operator": "Date()",
        "operands": [
          {
            "operator": "toDateString()",
            "operands": [
              {
                "operator": "Date()",
                "operands": [
                  "[$Expiration]"
                ]
              }
            ]
          }
        ]
      },
      {
        "operator": "Date()",
        "operands": [
          {
            "operator": "toDateString()",
            "operands": [
              {
                "operator": "Date()",
                "operands": [
                  {
                    "operator": "+",
                    "operands": [
                      "@now",
                      15768000000
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  },
  "then": {
    "additionalRowClass": "sp-css-backgroundColor-BgMintGreen sp-field-fontSizeSmall sp-css-color-MintGreenFont"
  },
  "else": {
    "if": {
      "operator": ">=",
      "operands": [
        {
          "operator": "Date()",
          "operands": [
            {
              "operator": "toDateString()",
              "operands": [
                {
                  "operator": "Date()",
                  "operands": [
                    "[$Expiration]"
                  ]
                }
              ]
            }
          ]
        },
        {
          "operator": "Date()",
          "operands": [
            {
              "operator": "toDateString()",
              "operands": [
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "+",
                      "operands": [
                        "@now",
                        7776000000
                      ]
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    },
    "then": {
      "additionalRowClass": "sp-css-backgroundColor-BgGold sp-field-fontSizeSmall sp-css-color-GoldFont"
    },
    "else": {
      "if": {
        "operator": ">=",
        "operands": [
          {
            "operator": "Date()",
            "operands": [
              {
                "operator": "toDateString()",
                "operands": [
                  {
                    "operator": "Date()",
                    "operands": [
                      "[$Expiration]"
                    ]
                  }
                ]
              }
            ]
          },
          {
            "operator": "Date()",
            "operands": [
              {
                "operator": "toDateString()",
                "operands": [
                  {
                    "operator": "Date()",
                    "operands": [
                      "@now"
                    ]
                  }
                ]
              }
            ]
          }
        ]
      },
      "then": {
        "additionalRowClass": "sp-css-backgroundColor-BgCoral sp-field-fontSizeSmall sp-css-color-CoralFont"
      },
      "else": {
        "additionalRowClass": "sp-css-backgroundColor-redDark sp-field-fontSizeSmall sp-css-color-redDarkFont"
      }
    }
  }
}

Yes, I have looked at the samples given and have tried using the excel formatting:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "additionalRowClass": "=if(([$Expiration] >= (@now + 15768000000)),'sp-css-backgroundColor-BgMintGreen sp-field-fontSizeSmall sp-css-color-MintGreenFont', if([$Expiration] >= (@now + 7776000000)),'sp-css-backgroundColor-BgGold sp-field-fontSizeSmall sp-css-color-GoldFont', if([$Expiration] >= @now),'sp-css-backgroundColor-BgCoral sp-field-fontSizeSmall sp-css-color-CoralFont','sp-css-backgroundColor-redDark sp-field-fontSizeSmall sp-css-color-redDarkFont')"
}

But again this doesn't work as expected either. I know the JSON formatting is correct, but it still will not change the rows as intended.

Any help is appreciated.

Answer 1

Here is the difference on JSON formatting between SharePoint 2019 and SharePoint Online.

• Excel-style expressions are not supported on SP 2019.
• View formatting is currently supported only in SharePoint Online.

Therefore, the "rowFormatter" and "additionalRowClass" do not work in SharePoint Server 2019.

For SharePoint 2019, you can use Client Side Rendering (CSR) which represents a rendering engine for list views.

Note: Use classic SharePoint mode

You can refer the code samples given in below links and modify it according to your conditions:

• How to highlight the row in red if expiration date is passed for SharePoint 2013 list
• SharePoint 2013 on-prem conditional formatting row by dueDate

=========================== Updated Answer =======================

Please use the below JSON formatting:

Note: Number of days *24 *60 *60 *1000. For example: six months = (6 * 30) *24 *60 *60 *1000

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "@currentField",
    "style": {
       "background-color": "=if(@currentField < @now, '#B22222', if(@currentField > @now && @currentField < @now+7776000000, '#F08080', if(@currentField > @now+7776000000 && @currentField < @now+15552000000, '#FFFF00', if(@currentField > @now+15552000000, '#90EE90', ''))))"
    }
}