0

I have a simple renewal list that has a date column this date column I would like to change color depending on these criteria.

  • If the date is within the next 14 days - Yellow
  • If the date matches today's date or is older - Red

I've have created the following JSON:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "attributes": {},
  "style": {
    "box-sizing": "border-box",
    "padding": "0 5px 5px",
    "background-color": {
      "operator": "?",
      "operands": [
        {
          "operator": "<=",
          "operands": [
            "[$Renewal_x0020_Date]",
            "@now"
          ]
        },
        "#fac3c3",
        {
          "operator": "?",
          "operands": [
            {
              "operator": "<=",
              "operands": [
                "[$Renewal_x0020_Date]",
                {
                  "operator": "+",
                  "operands": [
                    "@now",
                    1209600000
                  ]
                }
              ]
            },
            "#ffe682",
            ""
          ]
        }
      ]
    }
  },
  "children": []
}

That basically does what I need but I've noticed when a field doesn't have a date set that field is shaded Red like so:

enter image description here

I am assuming this is happening as even though it looks empty SharePoint date columns have the 1/1/1900 default date? Is there an easy way to exclude any fields that are "empty"?

3
  • Did you try below JSON, is it working for you? Nov 8, 2022 at 3:31
  • 1
    Thanks @GaneshSanap, that worked great and using the IF statements is a much easier way for my brain to understand!
    – RobC_CTL
    Nov 8, 2022 at 16:12
  • Great, glad it worked for you! Nov 8, 2022 at 16:30

1 Answer 1

1

In JSON Formatting, you can check if Date & Time column is blank like:

Number(@currentField) == 0

You can find other ways to check if date & time column is empty or not at: SharePoint JSON formatting: Check if date & time column is blank/empty

GitHub sample for reference: Formatting a column when a date column is blank


As you are using SharePoint online and adding this JSON on "Renewal Date" column, you can use JSON like below for your requirements:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "attributes": {},
  "style": {
    "box-sizing": "border-box",
    "padding": "0 5px 5px",
    "background-color": "=if(Number(@currentField) == 0, '', if(@currentField <= @now, '#fac3c3', if(@currentField <= addDays(@now, 14), '#ffe682', '')))"
  },
  "children": []
}

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.