Conditional Formatting ( Color Coding) a date field

Question

I am using the JSON Script below to create a list where records with Expiration date 10 days from @now will highlight RED; 20 Days from @ now will Highlight Yellow, and 60 Days From @ now will highlight Golden.

10 days & 20 days work. The 60 days does not. I edited an existing and changed only the milliseconds.

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

Answer 1

Maybe your milliseconds for 20 days are not correct, it should be 1728000000 (86400000 *20). After correcting it, I'm getting the below output. Is that the expected outcome?

Updating the response to show different color for days > 60 Please note that there is no predefined classes, so had to use background-color style

Updated JSON (if statement has been rearranged)

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "background-color": "=if(@currentField>@now + 5184000000,'#b5e7a0', if((@currentField >=@now + 1728000000) && (@currentField <@now + 5184000000) , '#d4ac6e',if((@currentField >=@now + 864000000) && (@currentField < @now + 1728000000), '#ffef96', '#f7cac9')))",
    "color": "black",
    "font-size": "1.25em"
  },
  "children": [
    {
      "elmType": "span",
      "style": {
        "display": "inline-block",
        "padding": "0 4px"
      },
      "attributes": {
        "iconName": "=if(@currentField <= @now + 1814400000,'Error', '')"
      }
    },
    {
      "elmType": "span",
      "txtContent": "@currentField"
    }
  ]
}

Answer 2

This is the formula to calculate days in Milliseconds

days in Milliseconds = Number of days * (24*60*60*1000)

So for 60 days, it should be 5184000000

But I think the issue is not related to Milliseconds, it's related to the condition itself where you should use && to define the period of time as the following:

Example

For 60 days, you have to check if the current date greater than 20 days and lower than or equal to 60 days.

If((@currentField <=@now + 5184000000) && (@currentField > @now + 1728000000))

Also, you should do the same for 20 days

If((@currentField <=@now + 1728000000) && (@currentField > @now + 864000000))

And finally, for 10 days, it should be

If((@currentField <=@now + 864000000)

Read more details about the complete JSON date from at JSON SharePoint Date Column Formatting in SharePoint 2019 and SharePoint Online

Answer 3

You got the capitalization wrong.@currentField F is uppercase, the lowercase f of your last if.

The number of milliseconds in 20 days is 1728000000.

Updated json:

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

Test result: