Column Formatting - Background based on dates, how to stop empty fields being included?

Question

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:

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"?

Answer 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": []
}