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
    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?

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

  • This is what I also thought this logic was too complex to be accomplished by JSON formatting. A Power Automate flow might be able to perform this behavior? Commented Apr 1, 2022 at 14:51
  • @Mathieu-Alex Yes, you could also do this using a flow. You would just have to deal with the "item modified loop" issue. In you're not familiar, just search for "power automate item modified loop issue" for a description of the issue and possible solutions. Commented Apr 1, 2022 at 14:55
  • Thank you @Rob Windsor! I'm aware of the infinite loop issue. I will try to develop a flow. Given that I'm not an expert in Power Automate, if you have any suggestions, I will be happy to hear them. Commented Apr 1, 2022 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.