Forum Discussion

spinman's avatar
spinman
Copper Contributor
Oct 26, 2019

JSON Formula Help

I am new to using JSON but I'm looking to do the following any help would be appreciated. I have a Sharepoint list that has a Date Column labeled Expiration Date and I'd like to show green if a date...
Lists
SharePoint Online
Matti Paukkonen's avatar
Matti Paukkonen
Iron Contributor
Oct 28, 2019

Hi spinman,

 

There was already a good example of docs.microsoft.com: https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting#apply-formatting-based-on-date-ranges under "Formatting items based on arbitrary dates (advanced)" title. I just tweaked a little bit to figure out, if field is empty.

 

Here is the example:

 

 

 

{
   "$schema": "<a href="<a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a>" target="_blank"><a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a</a>>",
   "elmType": "div",
   "txtContent": "@currentField",
   "style": {
      "background-color": 
      
      {
         "operator": "&&",
         "operands": [
           "=length([$DueDate]) > 0",{
            "operator": "?",
            "operands": [
               {
                  "operator": "<=",
                  "operands": [
                     "[$DueDate]",
                     {
                        "operator": "+",
                        "operands": [
                           "@now",
                           86400000
                        ]
                     }
                  ]
               },
               "#ff0000",
               "green"
            ]
           }
         ]
      }
   }
}

 

 

 

 

 You just need to change [$DueDate] to correspond your date time field, and if you copy example code directly, fix the schema as following (code snippet editor just adds unnecessary html tags): 

"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json"
 

 

Results are like this

 

Was this something you were looking for?

  • spinman's avatar
    spinman
    Copper Contributor
    Oct 29, 2019

    Hi Matti Paukkonen 

    Thanks for your help with this.  This works for this one.

     

    What about this.. I have a column called Effective Date and a column called Exp Date.  How would I go about coloring the Effective date green if its within 30 days of the Exp Date and red if its after 30 days of the exp date?

    • Matti Paukkonen's avatar
      Matti Paukkonen
      Iron Contributor
      Nov 04, 2019

      Hi spinman,

       

      Here is an example. 

       

      {
    "$schema": "<a href="<a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a>" target="_blank"><a href="https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a" target="_blank">https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json</a</a>>",
    "elmType": "div",
    "txtContent": "@currentField",
    "style": {
       "background-color": 
       
       {
          "operator": "?",
          "operands": [
            "=length([$EffectiveDate]) > 0",
            {"operator":"?",
            "operands":[
                {"operator":"<",
                "operands": [
                    "[$EffectiveDate]","[$ExpDate]"
                ]},"",
            
            {"operator":"?",
            "operands":[
                {"operator":"<","operands":[
                    "[$EffectiveDate]",
                    {
                       "operator": "+",
                       "operands": [
                          "[$ExpDate]",
                          2592000000
                       ]
                    }]},"green","red"

            ]},""
            ]}
            
            ,""
          ]
          
       }
    }
 }

       

       

      Notice that actual value for comparing one month is: 2592000000, it was wrong on my first example.

Resources