Forum Discussion
Solved
How to conditionally format a date column that is two weeks old?
Hi Sharepoint boffins,
I am trying to work how how to get a JSON conditional formatting code to work that will highlight a date cell that is two weeks' old and based on another cell being empty.
I have a code that will show me everything older than today's date but I don't want that, because I only want to see an item that hasn't been actioned after two weeks of the item being created. I thought changing the "@now" to "@now + 864000000" would do it but it hasn't so maybe I either have the number around the wrong way or this is not the correct place to put that function?
Below is what I have currently that shows me everything older than today's date:
{
"$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": [
{
"operator": "<",
"operands": [
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"[$Created]"
]
}
]
}
]
},
{
"operator": "Date()",
"operands": [
{
"operator": "toDateString()",
"operands": [
{
"operator": "Date()",
"operands": [
"@now"
]
}
]
}
]
}
]
},
{
"operator": "==",
"operands": [
"[$AIODecision]",
""
]
}
]
},
"sp-css-backgroundColor-BgRed sp-css-borderColor-CoralFont sp-field-fontSizeSmall sp-css-color-WhiteText",
""
]
}
},
"txtContent": "[$Created.displayValue]"
}
This is what I want it to look like:
Hopefully this makes sense and someone has the answer 🙂 Thank you in advance.
StephDH246
If you want to get the number of milliseconds since 01.01.1970, you need the expressionNumber(Date([$Created]))So if you want to select all Dates before two weeks ago you could use the formula
Number(Date([$Created]))<(Number(Date(@now))-1209600000(1209600000 = 14 * 24 * 60 *60 *1000).
You can access other fields in the view. If you have a field "CellToBeEmpty" and that field is in the view, then you can access the value of the field like this : [$CellToBeEmpty]
With two nested "if" statements, your json would look 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": "=if([$CellToBeEmpty],'',if(Number(Date([$Created]))<(Number(Date(@now))-1209600000),'sp-css-backgroundColor-BgRed sp-css-borderColor-CoralFont sp-field-fontSizeSmall sp-css-color-WhiteText',''))" }, "txtContent": "[$Created.displayValue]" }
2 Replies
StephDH246
If you want to get the number of milliseconds since 01.01.1970, you need the expressionNumber(Date([$Created]))So if you want to select all Dates before two weeks ago you could use the formula
Number(Date([$Created]))<(Number(Date(@now))-1209600000(1209600000 = 14 * 24 * 60 *60 *1000).
You can access other fields in the view. If you have a field "CellToBeEmpty" and that field is in the view, then you can access the value of the field like this : [$CellToBeEmpty]
With two nested "if" statements, your json would look 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": "=if([$CellToBeEmpty],'',if(Number(Date([$Created]))<(Number(Date(@now))-1209600000),'sp-css-backgroundColor-BgRed sp-css-borderColor-CoralFont sp-field-fontSizeSmall sp-css-color-WhiteText',''))" }, "txtContent": "[$Created.displayValue]" }SvenSieverding Thank you, this worked!
