Forum Discussion
JSON Formula Help
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):
Results are like this
Was this something you were looking for?
- spinmanOct 29, 2019Copper Contributor
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 PaukkonenNov 04, 2019Iron Contributor
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.