Forum Discussion

Wompwoh's avatar
Wompwoh
Copper Contributor
Sep 18, 2023

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

Hi All,

For context, I came across this post: https://techcommunity.microsoft.com/t5/sharepoint/create-an-age-days-old-column-in-sharepoint-list/m-p/464661#M29198

I'm trying to create a days old column that calculates on a daily basis the age of an item since it was last reported, but I'd like that calculation to stop when the item status is set to "Complete" and also persist (Keep) the last calculation as to keep record of how it took for the item to be finished. 

I see people said I can do this via Power Automate flow, but that was assuming the calculation continued on forever... Has anyone implemented this already and could share how they did this? 

Any Ideas?

SharePoint Online
  • Rob_Elliott's avatar
    Rob_Elliott
    Bronze Contributor
    Sep 19, 2023

    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)

Resources