1

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"
    }
  ]
}

3 Answers 3

2

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?

enter image description here

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"
    }
  ]
}

enter image description here

5
  • the value at 60 days from @now I want to be golden to distinguish from the Green - GOOD value at 63 days Dec 17, 2020 at 21:18
  • You mean, if >60, you want it to be a different color, right? Maybe greener, right? Let me see if there are any default class that we can use for that Dec 17, 2020 at 21:27
  • Basically if at 60 days to be a darker yellow - golden - the at 10 days is red and at 20 is yellow - the at 60 is still green - MANY thanks for your assistance! Dec 17, 2020 at 21:42
  • Fed 15 is 60 days out from today but still green Dec 17, 2020 at 21:43
  • Updated my post. Let know if you have any questions Dec 17, 2020 at 22:40
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

1

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

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: enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.