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...
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)
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "style": { "background-color": "=if(@currentField <= @now + 1296000000, 'red', (if(@currentField >= @now + 1296000000 && @currentField <= @now + 2592000000,'orange', ''))", "color": "=if(@currentField <= @now + 2592000000, 'white', 'black')", "padding-left": "10px" } }Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)- Thanks 🙂
- Thank you very much. Yours look way smoother.
I started by using the Conditional Formating in SharePoint, then switched in Advanced mode. So even though there are lots of lines, it was just minor modifying. 🙂
