Forum Discussion
Conditional Formatting for Dates using JSON
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" } }
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?
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.
LearningSharepoint you can do combine the 2 posts with the following JSON. Note, my column is name just Received - as I said before, I don't like using special characters in column names.
{ "$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" } }One thing to point out, when you are adding days to @now you can simplify it and not use millliseconds but use the addDays function instead. For example for greater than or equal to today + 30 days:
=if(@currentField >= addDays(@now, 30), ....Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)- Thank you - this code seems to work. The only issue I've noticed is when the received date is "today," which is colouring the cell red instead of leaving it neutral. Any ideas?
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" } }
