0

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

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.

| improve this answer | |
0

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"
    }
  ]
}
| improve this answer | |

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.