1

I have been attempting to format the colour of each row via the date in my $CONTRACTEXPIRES column.

I have the formatting set successfully to be red if the date has passed and green if it has not.

Where there is no date entered into $CONTRACTEXPIRES it is red and I require this to remain grey/none formatted if possible. I have scoured a lot of sites in order to attempt this, but they all refer to column formatting, not the row.

My current code:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "additionalRowClass": {
    "operator": ":",
    "operands": [
      {
        "operator": "<",
        "operands": [
          {
            "operator": "Date()",
            "operands": [
              {
                "operator": "toDateString()",
                "operands": [
                  {
                    "operator": "Date()",
                    "operands": [
                      "[$CONTRACTEXPIRES]"
                    ]
                  }
                ]
              }
            ]
          },
          {
            "operator": "Date()",
            "operands": [
              {
                "operator": "toDateString()",
                "operands": [
                  {
                    "operator": "Date()",
                    "operands": [
                      "@now"
                    ]
                  }
                ]
              }
            ]
          }
        ]
      },
      "sp-css-backgroundColor-BgCoral sp-css-color-CoralFont",
      {
        "operator": ":",
        "operands": [
          {
            "operator": ">",
            "operands": [
              {
                "operator": "Date()",
                "operands": [
                  {
                    "operator": "toDateString()",
                    "operands": [
                      {
                        "operator": "Date()",
                        "operands": [
                          "[$CONTRACTEXPIRES]"
                        ]
                      }
                    ]
                  }
                ]
              },
              {
                "operator": "Date()",
                "operands": [
                  {
                    "operator": "toDateString()",
                    "operands": [
                      {
                        "operator": "Date()",
                        "operands": [
                          "@now"
                        ]
                      }
                    ]
                  }
                ]
              }
            ]
          },
          "sp-css-backgroundColor-BgMintGreen sp-css-color-MintGreenFont",
          ""
        ]
      }
    ]
  }
}
8
  • What is the data type of $CONTRACTEXPIRES column? Date and Time or Single line of text? Dec 8, 2021 at 14:02
  • Hi Ganesh. It is formatted as UK date and time (with the time hidden) e.g: 31/03/2021 Dec 8, 2021 at 14:07
  • Unfortunately I am getting "Please enter valid view-formatting JSON" error message. Within sharepoint online the is also a message after "additionalRowClass" which is partly cut off from the screen - the visible message stating "...applied to the entire row. Supports expressions. Only valid for 'List' and 'Compact List' layouts. Incorrect type. Expected "object". Dec 10, 2021 at 7:10
  • what is the type of $CONTRACTEXPIRES column? Date and Time or Single line of text? Make sure you are entering this JSON in view formatting settings (check image given below in my answer). Dec 10, 2021 at 8:31
  • &CONTRACTEXPIRES is date and time format. I have double checked and I am entering the information in the exact way you described with the picture, but unfortunately it is still coming up with the same error decribed in previous comment Dec 10, 2021 at 14:30

1 Answer 1

0

Try using this JSON, it should work for you:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "additionalRowClass": "=if(Number([$CONTRACTEXPIRES]) == 0, '', if(Date(toDateString(Date([$CONTRACTEXPIRES]))) < Date(toDateString(Date(@now))), 'sp-css-backgroundColor-BgCoral sp-css-color-CoralFont', if(Date(toDateString(Date([$CONTRACTEXPIRES]))) > Date(toDateString(Date(@now))), 'sp-css-backgroundColor-BgCoral sp-css-color-CoralFont', 'sp-css-backgroundColor-BgMintGreen sp-css-color-MintGreenFont')))"
}

There might be a simple way to achieve what you want. But, I am not sure about your column formatted as UK date and time. So, I have kept all Date() & toDateString() operators as it is.

Documentation: Use view formatting to customize SharePoint


Update:

Enter JSON code using Format current view under Switch view options dropdown. Select format view & layout (List) options like below & paste code in advanced mode:

enter image description here

If above JSON is not working for you, try this:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "additionalRowClass": "=if(Number([$CONTRACTEXPIRES]) == 0, '', if([$CONTRACTEXPIRES] < @now, 'sp-css-backgroundColor-BgCoral sp-css-color-CoralFont', if([$CONTRACTEXPIRES] > @now, 'sp-css-backgroundColor-BgCoral sp-css-color-CoralFont', 'sp-css-backgroundColor-BgMintGreen sp-css-color-MintGreenFont')))"
}

See other ways to check if date column is empty at: SharePoint JSON formatting: Check if date & time column is blank/empty

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.