Can't sort on calculated "Elapsed Days" column in a View

[DaveStorey]

I have a calculated column to show the elapsed days between two dates, but when I place this field on a View, the sorting options are disabled. 

I see in the documentation that this is a known limitation in the following conditions:

• Sorting is disabled on:
  • A calculated column that contains a column of a parent row.
  • A calculated column that contains a logical column (for example, address column)
  • A calculated column that contains another calculated column.

My situation doesn't seem to satisfy any of these 3 conditions, as I am only comparing 2 date fields on the main entity.  I have added an attachment showing the details of my calculated field.

As a work-around, I created a separate non-calculated field, and added a business rule to copy the data from the calculated field to the non-calculated field, but of course this doesn't keep the non-calculated field up-to-date because the business rule only runs when the record is opened in the form, therefore the values get out-of-sync very often.

Questions:

- Is there a way to have a calculated field sortable on a View?

- Is there a different reliable method of showing an "Elapsed Days" value in a View, and that is a sortable column?

Thank you

[kleinda]

Hello, did you have any luck regarding solving this issue?

[DaveStorey]

Hi, the only way I could resolve this issue was to create a second field and update it nightly using Power Automate.  Here are my notes:

Problem:  Calculated fields that reference other calculated fields (or even functions such as Now()) are not sortable in a View

This workaround uses these steps:

1. Create a second ‘copy’ field “elapseddays_nbr”
1. Ensure this ‘copy’ field is not included in Auditing
2. Create a Business Rule to populate this ‘copy’ field with the value from the calculated field
1. This just makes sure that when a user makes a change to a record using the Main Form, the field on the View will also be correct immediately
3. Add the actual calculated field to the Main Form(s)
4. Add the ‘copy’ field to the respective View(s)
5. Create a scheduled Power Automate Flow to copy the calculated field’s value to the ‘copy’ field nightly