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 is in four, three or two days background color should be yellow
If date is in one day or it is today background color should be red
Else background color should not be changed
I was able to find and modify different JSON code to partly achieve this goal but I am just not able to finish it. Mostly my problem is the else-part.
Can anyone help me out?
Thank you very much in advance!
8 Replies
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)
