Calculate Days old column to update daily and stop when item is complete, but keep last calculation.

Wompwoh you can't do this with a calculated column because those don't update dynamically, but you CAN do this with a flow in Power Automate. I have a date time column called Reported, a choice column called Status2 with the options New, Progressing and Complete, and a number column called Days Old with no decimal places.

The flow ran and calculated the days between today's date and the reported date. Note particularly the Guadeloupe item which is 35 days old  and which, during today, will be changed to Complete:

The flow 

1. The flow has a recurrence schedule trigger set to run once a day at 3am:

2. The first action is the SharePoint get items but you don't want to be doing the calculation if  the Status2 column has a value of Complete. So add a filter query of Status2 ne 'Complete' so it won't bring back any items in the list which are complete. ne stands for not equal to. This is much more efficient than using a condition action. I've limited the columns to just those in the Days Old View as I have a lot of unrelated columns in other views.

3. Next, add a compose action, click in the inputs field then the dynamic content box will open and you click on the Expression tab and add the following expression:
formatDateTime(utcNow(),'yyyy-MM-dd')

4.  Add another compose action, click in the inputs field and select the Reported column from the dynamic content box. You will see that it gets put inside an apply to each which is expected behaviour.

Next and still inside the apply to each, add another compose action which will have the following expression to calculate the difference between the today compose and the reported compose:
div(sub(ticks(outputs('ComposeToday')),ticks(outputs('ComposeReported'))),864000000000)

5. Finally, and still inside the apply to each, add a SharePoint update item action. For the ID field select ID from the get items section of the dynamic content box. For the Days Old field, from the dynamic content box select the outputs of the Compose where you calculated the date difference:

During today the Guadeloupe item has been changed to Complete. I've amended the flow as though we are now at tomorrow and the flow has updated the other items with the new days old figure but has not changed Guadeloupe which is - and will always remain - at 35 days.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)