Forum Discussion
SharePoint Online List - Format column based on a date range
Hello community, I want to change the background color of a date column in my SharePoint Online list based on the date that has been written in the field. My goal is the following: If date i...
I tried this code, but it did not work:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"style": {
"box-sizing": "border-box",
"padding": "0 2px"
},
"attributes": {
"class": {
"operator": ":",
"operands": [
{
"operator": "&&",
"operands": [
{
"operator": ">=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now - 432000000"
]
}
]
}
]
}
]
},
{
"operator": "<=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now - 172800000"
]
}
]
}
]
}
]
}
]
},
"sp-css-backgroundColor-BgGold sp-css-borderColor-GoldFont",
{
"operator": ":",
"operands": [
{
"operator": "&&",
"operands": [
{
"operator": ">=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now - 86400000"
]
}
]
}
]
}
]
},
{
"operator": "<=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now"
]
}
]
}
]
}
]
}
]
},
"sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont",
""
]
}
]
}
},
"children": [
{
"elmType": "span",
"style": {
"line-height": "16px",
"height": "14px"
},
"attributes": {
"iconName": {
"operator": ":",
"operands": [
{
"operator": "&&",
"operands": [
{
"operator": ">=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now - 432000000"
]
}
]
}
]
}
]
},
{
"operator": "<=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now - 172800000"
]
}
]
}
]
}
]
}
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": "&&",
"operands": [
{
"operator": ">=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now - 86400000"
]
}
]
}
]
}
]
},
{
"operator": "<=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now"
]
}
]
}
]
}
]
}
]
},
"",
""
]
}
]
},
"class": {
"operator": ":",
"operands": [
{
"operator": "&&",
"operands": [
{
"operator": ">=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now - 432000000"
]
}
]
}
]
}
]
},
{
"operator": "<=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now - 172800000"
]
}
]
}
]
}
]
}
]
},
"sp-css-color-GoldFont",
{
"operator": ":",
"operands": [
{
"operator": "&&",
"operands": [
{
"operator": ">=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now - 86400000"
]
}
]
}
]
}
]
},
{
"operator": "<=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now"
]
}
]
}
]
}
]
}
]
},
"sp-css-color-CoralFont",
""
]
}
]
}
}
},
{
"elmType": "span",
"style": {
"overflow": "hidden",
"text-overflow": "ellipsis",
"padding": "0 3px"
},
"txtContent": "[$Versand.displayValue]",
"attributes": {
"class": {
"operator": ":",
"operands": [
{
"operator": "&&",
"operands": [
{
"operator": ">=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now - 432000000"
]
}
]
}
]
}
]
},
{
"operator": "<=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now - 172800000"
]
}
]
}
]
}
]
}
]
},
"sp-field-fontSizeSmall sp-css-color-GoldFont",
{
"operator": ":",
"operands": [
{
"operator": "&&",
"operands": [
{
"operator": ">=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now - 86400000"
]
}
]
}
]
}
]
},
{
"operator": "<=",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Versand]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now"
]
}
]
}
]
}
]
}
]
},
"sp-field-fontSizeSmall sp-css-color-CoralFont",
"sp-field-fontSizeSmall"
]
}
]
}
}
}
]
}Got the Solution. The problem was that I cannot calculate with @now - date, You have to put the operator before the @now.
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "style": { "box-sizing": "border-box", "padding": "0 2px" }, "attributes": { "class": { "operator": ":", "operands": [ { "operator": "&&", "operands": [ { "operator": ">=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "-", "operands": [ "@now", 432000000 ] } ] } ] } ] }, { "operator": "<=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "-", "operands": [ "@now", 172800000 ] } ] } ] } ] } ] }, "sp-css-backgroundColor-BgGold sp-css-borderColor-GoldFont", { "operator": ":", "operands": [ { "operator": "&&", "operands": [ { "operator": ">=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "-", "operands": [ "@now", 86400000 ] } ] } ] } ] }, { "operator": "<=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "@now" ] } ] } ] } ] } ] }, "sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont", "" ] } ] } }, "children": [ { "elmType": "span", "style": { "line-height": "16px", "height": "14px" }, "attributes": { "iconName": { "operator": ":", "operands": [ { "operator": "&&", "operands": [ { "operator": ">=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "-", "operands": [ "@now", 432000000 ] } ] } ] } ] }, { "operator": "<=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "-", "operands": [ "@now", 172800000 ] } ] } ] } ] } ] }, "", { "operator": ":", "operands": [ { "operator": "&&", "operands": [ { "operator": ">=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "-", "operands": [ "@now", 86400000 ] } ] } ] } ] }, { "operator": "<=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "@now" ] } ] } ] } ] } ] }, "", "" ] } ] }, "class": { "operator": ":", "operands": [ { "operator": "&&", "operands": [ { "operator": ">=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "-", "operands": [ "@now", 432000000 ] } ] } ] } ] }, { "operator": "<=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "-", "operands": [ "@now", 172800000 ] } ] } ] } ] } ] }, "sp-css-color-GoldFont", { "operator": ":", "operands": [ { "operator": "&&", "operands": [ { "operator": ">=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "-", "operands": [ "@now", 86400000 ] } ] } ] } ] }, { "operator": "<=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "@now" ] } ] } ] } ] } ] }, "sp-css-color-CoralFont", "" ] } ] } } }, { "elmType": "span", "style": { "overflow": "hidden", "text-overflow": "ellipsis", "padding": "0 3px" }, "txtContent": "[$Versand.displayValue]", "attributes": { "class": { "operator": ":", "operands": [ { "operator": "&&", "operands": [ { "operator": ">=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "-", "operands": [ "@now", 432000000 ] } ] } ] } ] }, { "operator": "<=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "-", "operands": [ "@now", 172800000 ] } ] } ] } ] } ] }, "sp-field-fontSizeSmall sp-css-color-GoldFont", { "operator": ":", "operands": [ { "operator": "&&", "operands": [ { "operator": ">=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "-", "operands": [ "@now", 86400000 ] } ] } ] } ] }, { "operator": "<=", "operands": [ { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "[$Versand]" ] } ] } ] }, { "operator": "Date()", "operands": [ { "operator": "toDateString()", "operands": [ { "operator": "Date()", "operands": [ "@now" ] } ] } ] } ] } ] }, "sp-field-fontSizeSmall sp-css-color-CoralFont", "sp-field-fontSizeSmall" ] } ] } } } ] }andreaskelly wow, those 716 lines of JSON just give me a headache. It was possible with 8!!
The JSON is:
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "style": { "background-color": "=if((@currentField <= @now + 86400000), 'red', if((@currentField >= @now + 86400000 && @currentField <= @now + 345600000),'orange',''))" } }Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)RobElliott , I need the same but for 15 days reminder column before the expiration date column (red) and 30 days before expiration date (yellow)
