Forum Discussion

eenochs-turner's avatar
eenochs-turner
Brass Contributor
Apr 30, 2020

JSON Date Range Help

Is it possible using JSON to create a text color change based on a date range.  For example I have a field that shows when a warranty starts.  Warranty is good for a year.  I would like the text to change if its outside that date range.  For example if the field shows 3/1/2020 and today is 4/30/2020.  The text would still be black between the warranty wouldn't expire till 3/1/2021.  

SharePoint Online
  • RobElliott's avatar
    RobElliott
    Silver Contributor
    Apr 30, 2020

    eenochs-turner yes it's possible to do this. Up until the warranty expiry date in the example below the date is colored black, but if the warranty expiry is earlier than today it's red.

     

     

     

    The syntax for the JSON on the Warranty column is:

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField",
  "style": {
    "color": "=if([$Warranty] < @now, '#c64444', '#000000')"
  }
}

     

    But you can also format it with the built-in standard column formatting without any need for JSON:

     

    Rob
    Los Gallardos
    Microsoft Power Automate Community Super User

     

     

    • eenochs-turner's avatar
      eenochs-turner
      Brass Contributor
      May 05, 2020

      RobElliott 

       

      Rob thank you for the response but i went a different route and I'm almost there.  This is the JSON command i went with and its working but now i have a issue with blank entries.  

       

      {

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

        "elmType": "div",

        "debugMode": true,

        "txtContent": "@currentField",

        "style": {

          "color": "=if([$EMR_x0020_Effective_x0020_Date] +31556952000 <= @now, '#ff0000', '#000000')"

        }

      }

       

      The Issue I'm having now is how can i add a statement to this to ignore any entry that is blank.  In this list there will be blank entries as not every item has a warranty period.  

    • DaveTheVoice's avatar
      DaveTheVoice
      Copper Contributor
      Dec 01, 2023

      RobElliott  As @eenochs-turner had the same issue I can't get mine to work as well. The field is a Calculated field and I need to "highlite" 3 different ranges: where the number is >60 days Green, between 59 and 31 days Amber, and < 30 days Red.

      What am I doing wrong?

      {
      "$schema":"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
      "elmType":"div",
      "txtContent":"@currentField",
      "style":{
      "background-color":"=if(Number(@currentField)==0,'#c27ba0',if(@currentField <=@now - (365-346)*24*60*60*1000, 'red',if(@currentField <= @now -(365-347)*24*60*60*1000,'orange',if(@currentField <= @now -(365-352)*24*60*60*1000,'yellow','limeGreen'))))",
      "color":"=if(Number(@currentField)==0, '#c27ba0',if(@currentField <= @now - (365-0)*24*60*60*1000,'White',if(@currentField <= @now -(365-60)*24*60*60*1000,'Black',if(@currentField <= @now -(365-60)*24*60*60*1000,'Black','Black'))))",
      "font-weight":"700",
      "text-align":"center",
      "display":"inline",
      "vertical-align":"middle",
      "line-height":"3em"
      }
      }

Resources