Forum Discussion
Formatting a Date Column to be red if expired, and gold is expiring within 5 days.
I know similar things have been asked but I can't find any available solutions that I could successfully tweak to work. Whenever I think I get close the formatting just makes the dates disappear 🤦:female_sign:
I have a column for "GL Expire" dates. I need to make it so that dates before today (aka Expired) turn "Red" aka "sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-css-color-CoralFont"
And dates between today and five days from today turn "Yellow" aka "sp-css-backgroundColor-BgGold sp-css-borderColor-GoldFont sp-css-color-GoldFont".
The current code in there is making the expired turn red, the ones that are today turn cyan, and any others have no color.
How can I update this to do what I need?
It currently looks like this:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"style": {
"box-sizing": "border-box",
"padding": "0 2px",
"overflow": "hidden",
"text-overflow": "ellipsis"
},
"attributes": {
"class": {
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
"@currentField",
""
]
},
"",
{
"operator": ":",
"operands": [
{
"operator": "<",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-css-color-CoralFont",
{
"operator": ":",
"operands": [
{
"operator": "==",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"sp-css-backgroundColor-BgCyan sp-css-borderColor-CyanFont sp-css-color-CyanFont",
{
"operator": ":",
"operands": [
{
"operator": ">",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@currentField"
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
"@now"
]
}
]
}
]
},
"",
""
]
}
]
}
]
}
]
}
},
"txtContent": "@currentField.displayValue"
}
I was able to solves this using AI (after MUCH back and forth and rewording).
The code that worked is ...
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "attributes": { "class": "=if(Number(@currentField) == 0, '', if(@currentField <= @now, 'sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-field-fontSizeSmall sp-css-color-CoralFont', if(@currentField <= @now + 432000000, 'sp-css-backgroundColor-BgGold sp-css-borderColor-GoldFont sp-field-fontSizeSmall sp-css-color-GoldFont', '')))" } }
I hope this helps someone else!!
- Jennife_rCopper Contributor
I was able to solves this using AI (after MUCH back and forth and rewording).
The code that worked is ...
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "@currentField", "attributes": { "class": "=if(Number(@currentField) == 0, '', if(@currentField <= @now, 'sp-css-backgroundColor-BgCoral sp-css-borderColor-CoralFont sp-field-fontSizeSmall sp-css-color-CoralFont', if(@currentField <= @now + 432000000, 'sp-css-backgroundColor-BgGold sp-css-borderColor-GoldFont sp-field-fontSizeSmall sp-css-color-GoldFont', '')))" } }
I hope this helps someone else!!