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 is entered and is it not within 30 days of expiring.  If the expiration date is 30 days or less of expiring I’d like it to turn red.

 

Lists
SharePoint Online

3 Replies

  • 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