Column formatting dates (including blank values)

Question

I'd like to create a column formatting where values should be: Green if expiration date > now()+60 days Yellow if expiration date < now()+60 days and > now() Red if expiration date <= now() No color if Expiration date column is empty.

I've created this JSON but it doesn't work (the blank date column is still in red color):

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
    "class": "= if([$Visa_Expiration] = '',  'sp-field-severity--good' ,  if([$Visa_Expiration] <= @now+86400000*60    && [$Visa_Expiration] > @now       , 'sp-field-severity--warning', if([$Visa_Expiration] <= @now, 'sp-field-severity--blocked', if([$Visa_Expiration] > @now+86400000*60, 'sp-field-severity--good', 'sp-field-severity--good')  )    ))"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {

      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

Any Ideas?

Rodolfo

Answer 1

Try the below JSON snippet to avoid the date column is blank,

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "attributes": {
        "class": "= if(Number([$Visa_Expiration]) == 0,  '' ,  if([$Visa_Expiration] <= @now+86400000*60    && [$Visa_Expiration] > @now       , 'sp-field-severity--warning', if([$Visa_Expiration] <= @now, 'sp-field-severity--blocked', if([$Visa_Expiration] > @now+86400000*60, 'sp-field-severity--good', 'sp-field-severity--good')  )    ))"
    },
    "children": [
        {
            "elmType": "span",
            "style": {
                "display": "inline-block",
                "padding": "0 4px"
            },
            "attributes": {}
        },
        {
            "elmType": "span",
            "txtContent": "@currentField"
        }
    ]
}

Use the Number([$Visa_Expiration]) == 0 to check whether the value for Date Column is blank. Hope this solves your problem.

Answer 2

Here it's the code:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "attributes": {
    "class": "= if(Number([$Visa_Expiration]) == 0,  '' , if([$Visa_Expiration] <= @now, 'sp-field-severity--blocked', if([$Visa_Expiration] <= @now+86400000*60, 'sp-field-severity--warning', if([$Visa_Expiration] > @now+86400000*60, 'sp-field-severity--good', ''))))"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": "=if(Number([$Visa_Expiration]) == 0,'',if([$Visa_Expiration] <= @now, 'ErrorBadge', if([$Visa_Expiration] <= @now+86400000*60, 'Error', if([$Visa_Expiration] > @now+86400000*60, 'CheckMark', ''))))"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}