Forum Discussion

Joanne2023's avatar
Joanne2023
Copper Contributor
Aug 11, 2023

Auto Days Open in a Sharepoint List - How do I add IF qualifier and color-coding?

Hello,

I am developing a tracking table using a sharepoint list that will be used on a Teams site.   I need to have a column that shows the # of days a record is open (Days Open) once it is entered into the list (Date Reported).  The field needs to be auto updated based on TODAY.  I've been able to get a version of JSON in column formatting to work to display the Days Open per the below:

{

  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

  "elmType": "div",

  "txtContent": "=floor((Number(@now)-Number([$DateReported]))/86400000)"

}

But, I want to do two things additional:  1) if the record is closed (Date Closed), I want the Days Open = 0, and if the Days Open is more than 90 days, I want the value colored red.  I tried to update the coding per the below but it is not working and I can't figure out the reason.  In the list setting, column format, there is no indication of any error but the value of the string is what shows up in the column, not a resultant.  I tried changing the column type from Single Text to Number with no improvement.  

{

  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",

  "elmType": "div",

  "txtContent": "=if(ISBLANK([$DateClosed]), ‘0’, ‘=floor((Number(@now)-Number([$DateReported]))/86400000)’)",

  "style": {

    "Color": "=if(floor(Number(@now)-Number([$DateReported]))/86400000) > 90,'#ff0000','')"

  }

}

Any help or guidance would be appreciated.

Thank you, Joanne2023

No RepliesBe the first to reply

Resources