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 c...
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

 

 

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