Forum Discussion
Conditional Formatting for Dates using JSON
Hello,
I am trying to use conditional formatting for MS List. I specifically want the background colour of the date to change based upon the number of days over since the file was received. Something along the lines of 30 days = yellow, 60 days = orange, and 90 days = red. After searching here and elsewhere I've managed to put together some JSON that I believe should work, but alas it does not. There is no error shown, but the table appears untouched. Any help is appreciated.
This is what I have so far:
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": "@currentField",
"style": {
"background-color": "=""=if([$DateReceived] <= @now + 7776000000, if([$DateReceived] <= @now + 5184000000, if([$DateReceived] <= @now + 2592000000 'sp-css-backgroundColor-red', 'sp-css-backgroundColor-BgOrange', 'sp-css-backgroundColor-warningBackground40'), '')")))",
"padding-left": "10px"
}
}
Otherwise try this (think I found the cause of it, in the code you used):
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "txtContent": "=floor((Number(@now)-Number([$Received]))/86400000)", "style": { "background-color": "=if((floor((Number(@now)-Number([$Received]))/86400000) >= 0 && floor((Number(@now)-Number([$Received]))/86400000) <=30, '#f0f0f0', if((floor((Number(@now)-Number([$Received]))/86400000) > 30 && floor((Number(@now)-Number([$Received]))/86400000) <=60, 'yellow', if((floor((Number(@now)-Number([$Received]))/86400000) > 60 && floor((Number(@now)-Number([$Received]))/86400000) <=90, 'orange','red')", "color": "=if((floor((Number(@now)-Number([$Received]))/86400000) > 90 , 'white', 'black')", "padding-left": "10px" } }
7 Replies
LearningSharepoint Is this the same column that you did the calculation of days since received post on?
Because then I would just add to that Column formatting....
{ "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", "elmType": "div", "attributes": { "class": "=if((floor((Number(@now)-Number([$DateReceived]))/86400000) >= 90, 'sp-css-backgroundColor-red', if((floor((Number(@now)-Number([$DateReceived]))/86400000) >= 60, 'sp-css-backgroundColor-BgOrange', if((floor((Number(@now)-Number([$DateReceived]))/86400000) >= 30, 'sp-css-backgroundColor-warningBackground40', '')))" }, "style": { "padding-left": "10px" }, "txtContent": "=floor((Number(@now)-Number([$DateReceived]))/86400000" }Replace the existing formatting in your DaysSinceRecieved column with the above
There are a few issues with your code. First of all "sp-css-backgroundColor-xxx" can not be referenced in the Styles section. It is a class that needs to be set as part of the attributes. Second, the order of your ifs were wrong. Try the above code, I have corrected the order, but also note, that I haven't used your calculation from this post, but from the other post, since I could not get it to work. So switched to the calculation we have already done, and compared that to the no of days, you want to differentiate between. If that can't be used, can you add what the @currentfield value is containing?
- Thanks for your response.
I was using the $DateReceived (date) column instead of the $DaysSinceReceived (number). I did this because I couldn't find examples elsewhere that matched my circumstances.
I've added the JSON so the $DaysSinceReceived column and nothing seems to happen. This is my first time adding two sets of JSON code together; do I just plop this underneath the JSON from my other post, or do I need to modify it somehow to work together?LearningSharepoint My code includes our calculation from the previous post, so already combined the two. So take my new code from my previous reply and replace all of the existing formatting on the DaysSinceRecieved column and you should be good.
