Forum Discussion
Odd behavior on a column with conditional formatting based on other columns
I made a simple SharePoint list to track Incidents. There is a column for [Incident Date] and [Complete Date] and a [Status] column for "Closed" or "In Progress". We want to be able to show "Days Open" in the list so we tried to create a [Days Open] calculated column which was simply
If([Status] = "Closed', [Complete Date] - [Incident Date], Today() - [Incident Date])
Unfortunately, we quickly relized that SP will only recalculate Today() when an item is added or edited.
After some more research I saw I could use the JSON column formatting to accomplish what I wanted so I created a dummy column [Days Open] configured as single line of text with the default text of "Days Open Placeholder". I setup the JSON shown at the bottom of the post.
This works perfectly if I have a view that only shows me list items where [Status] is "In Progress" OR "Completed". When I am in a view that shows me All Items, the [Days Open] column only shows the open days for items where [Status] = Completed. The list items that are still "In Progress" have blanks.
The JSON is configured to check if the Status is "Completed" and if it is completed, show the number of day it took to complete by [Completed Date] - [Incident Date] and if it is not completed it caluates @now - [Incident Date] to show the total days open. The JSON will also set the background color based on the Days Open value as Pink, Yellow, or Lime.
JSON