Forum Discussion
Formatting Dates in SharePoint Online list
- The logic in the JSON doesn't seem to match what you asked for - what it is doing is showing Effective Date green if the Effective Date is less than ExpDate+1month, red if it's after that.
You seem to want Effective Date green if the ExpDate is less than a month away, and red if it's after the ExpDate... That's more complex logic. I'm not that great in JSON, but in pseudo logic it would be something like
If EffectiveDate < ExpDate and EffectiveDate >= ExpDate-1month show green
If Effective Date > ExpDate and EffectiveDate <= ExpDate+1month show red
spinman There's an answer from Matti Paukkonen to your previously asked question https://techcommunity.microsoft.com/t5/SharePoint/JSON-Formula-Help/m-p/977223#M35868 - did that not work for you?
There's great documentation here https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting
Robin Nilsson I tried to use the JSON provided but SharePoint gives me an error that says "Please enter valid column-formatting JSON.
spinman There's an error in the $schema code. Change this:
"$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>>",
to this:
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
Robin Nilsson Thanks. So I made that change and it let me save it. However, the Exp Date doesn't show up unless you go into the record and the field is not colored red or green. Here is what I put in the JSON format field of the Exp Date field. Am I still missing something?
{ "$schema": "<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>", "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" ] }, "" ] }, "" ] } } }
