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",
          ""
        ]
      }
    ]
  }
}
Improve this question
8
  • What is the data type of $CONTRACTEXPIRES column? Date and Time or Single line of text?
    – Ganesh Sanap - MVP
    Commented 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
    – Paul Thompson
    Commented 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".
    – Paul Thompson
    Commented 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).
    – Ganesh Sanap - MVP
    Commented 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
    – Paul Thompson
    Commented Dec 10, 2021 at 14:30

1 Answer 1

Reset to default
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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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