Format background color for SharePoint List items in a Calendar view using JSON and relative dates

Question

I have a SharePoint List that has a calendar view, where the date is based upon a column titled ExpirationDate.

I would like to configure the calendar view so that items where the ExpirationDate between today and 7 days from today have a red background color. Unfortunately, the Conditional Formatting option has exactly one relative date, and that's Today. I've tried using the Conditional Formatting option to get the base code and modify, but when I replace the static date 7 days from now with "@now + 604800000" it doesn't recognize the date.

Also, for some reason my ExpirationDate column is referenced as [$Expiration_x0020_Date]--which I can only assume was because the title originally had a space in it.

Code generated by SP:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "additionalEventClass": {
    "operator": ":",
    "operands": [
      {
        "operator": "&&",
        "operands": [
          {
            "operator": ">=",
            "operands": [
              {
                "operator": "Date()",
                "operands": [
                  {
                    "operator": "toDateString()",
                    "operands": [
                      {
                        "operator": "Date()",
                        "operands": [
                          "[$Expiration_x0020_Date]"
                        ]
                      }
                    ]
                  }
                ]
              },
              {
                "operator": "Date()",
                "operands": [
                  {
                    "operator": "toDateString()",
                    "operands": [
                      {
                        "operator": "Date()",
                        "operands": [
                          "@now"
                        ]
                      }
                    ]
                  }
                ]
              }
            ]
          },
          {
            "operator": "<=",
            "operands": [
              {
                "operator": "Date()",
                "operands": [
                  {
                    "operator": "toDateString()",
                    "operands": [
                      {
                        "operator": "Date()",
                        "operands": [
                          "[$Expiration_x0020_Date]"
                        ]
                      }
                    ]
                  }
                ]
              },
              {
                "operator": "Date()",
                "operands": [
                  {
                    "operator": "toDateString()",
                    "operands": [
                      {
                        "operator": "Date()",
                        "operands": [
                          "Thu Sep 14 2023"
                        ]
                      }
                    ]
                  }
                ]
              }
            ]
          }
        ]
      },
      "=if(@isSelected == true, 'sp-css-color-WhiteFont sp-css-backgroundColor-BgDarkRed' , 'sp-css-backgroundColor-BgCoral sp-css-color-CoralFont')",
      ""
    ]
  }
}

As I said, tried to sub out the specified date with a relative date, no dice.

Answer 1

Try using JSON formatting code like below for your SharePoint online modern list calendar view:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "additionalEventClass": "=if([$Expiration_x0020_Date] >= @now && [$Expiration_x0020_Date] <= addDays(@now, 7), if(@isSelected == true, 'sp-css-color-WhiteFont sp-css-backgroundColor-BgRed', 'sp-css-backgroundColor-BgDustRose sp-css-color-DustRoseFont')+' sp-field-fontSizeSmall', if(@isSelected == true, 'sp-css-color-WhiteFont sp-css-backgroundColor-BgGray' , 'sp-css-backgroundColor-BgLightGray sp-css-color-LightGrayFont')+' sp-field-fontSizeSmall')"
}

Where Expiration_x0020_Date is the internal name of your SharePoint list column. 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?

Related Reference: SharePoint: Highlight selected list item rows using JSON formatting - @isSelected