Conditional Duration Column in SharePoint Online

Question

We have a SPO list with a metadata column named Status of type choice that contains the following values:

• Opened
• Closed
• Re-opened

We want to create another column called Case Duration that will automatically calculate the number of days that have elapsed since the creation of an item in the list. However, this duration must be conditional that is to say trigger according to the value of the status column:

• When the Case Status field is initially set to open, start the Case Duration field.

• When the Case Status field change to close, stop Case
  Duration field and display something like: Case closed after X
  days been active.

• When the Case Status field is set to re-open, trigger and
  restart the Case Duration field.

I wrote this code to format the column with JSON, but this does not take into account the conditionality mentioned above:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=floor((Number(@now)-Number([$Date_x0020_and_x0020_Time_x0020_]))/(1000*60*60*24)) + ' day(s)'" 
}

Does anyone have a possible solution?

Answer 1

I'm not an expert on JSON formatting, but my gut tells me that logic may be too complex to implement using it. If custom coding is an option then I would suggest looking at using a SharePoint Framework Field Customizer Extension. That type of logic will be much easier to implement using code. See Build your first Field Customizer extension for more information.