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. Somet...

microsoft lists

Hanne_Lauritzen

Oct 24, 2024

LearningSharepoint

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

Copper Contributor

Oct 24, 2024

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?

Hanne_Lauritzen

Iron Contributor

Oct 24, 2024

LearningSharepoint

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"
  }
}

Forum Discussion

Conditional Formatting for Dates using JSON

Resources