Solved: Calculate time between two entries

mmamesc1b · ‎05-31-2023

Hello!

I am looking to calculate the time that has passed between two entries in a list.

This is what it looks like in Excel, and I need a SharePoint list to do this as well:

I have a flow that starts when a new item is added to the list, I'm just not sure what to do from here (or if I'm in the right place to start with)

Thank you in advance for your help!

rzaneti · ‎05-31-2023

Hi @mmamesc1b ,

Perfect! Check the steps below.

SharePoint List Structure

Step 1: Trigger/Get Items

I guess that you are already familiar with this part: you just have to trigger your flow from the insertion of a new item to the List and, after the trigger, run the 'Get Items' action (Note: 'Get Items', not 'Get Item'). No filters are needed for the 'Get Items' action.

Step 2: Get the Index of old task

Now we we need to get the ID of the task that was just finished, it means, the second-last item of your List. In the previous message, I mentioned that you could just take the ID of the last item and subtract 1 from that, but it is not accurate (if your users delete any record and entry it again, the logic will break).

So as an alternative, you will get the index from the second-last item of the 'Get Items' output and access the ID of it. To make the process clearer, I'm storing the index in a variable, and I use the following expression to get it: sub(length(outputs('Get_items')?['body/value']), 2)

The "sub()" expression works as a subtraction, and we are basically capturing the length of the 'Get items' array and subtracting 2 values from it (Power Automate arrays are zero-indexed, so they start from 0, 1, 2...).

Note: for mistake, I named the variable as "finished_task_id". However, it is not storing the task id, but only the task index 🙂

Step 3: Get the old task record

Now we need to isolate the old task from the array. To do it, you just need to pass the index between squared brackets after the 'Get Items' output, assigning this result to a "Compose" action. The compose action will basically creates a new object (in our case, will be just a clone of the object that represents the old record), and you will use this expression in the input:

outputs('Get_items')?['body/value'][variables('finished_task_id')]. Note that we are passing the index variable between [ ] after the output of the value from 'Get items' action.

Step 4: Calculate the time difference

Finally, we will be able to calculate the time difference between the old and the new task! I recommend you to store it in a variable (make sure to assign to a String variable, and to let the Data Type of your List column in SharePoint as single line of text), but you can add the formula directly to the 'Update item' action if you prefer.

You will use this expression as value assigned to the variable:

dateDifference(outputs('Compose')['Starttime'], triggerOutputs()?['body/Starttime']). Make sure to add the old task start time as first argument and the new task start time as second (otherwise, you will have a negative number as result).

Step 5 (final): Update the List

Then you can update your list record. Make sure to fill all fields from Compose output (it will overwrite the record, and if you leave it empty, the records will be replaced with blanks). To get the outputs from Compose, you will need to follow this pattern: outputs('Compose')['COLUMNAME'] (just replace COLUMN for your actual column name).

The result will appear in your List like this:

Let me know if it works or if you need anything else!!

View solution in original post

rzaneti · ‎05-31-2023

Hi @mmamesc1b ,

You are in the right path. Based on your description and on the shared images, you must have in mind that for each new entry in your SP List, you will be updating the "spent time" of the previous task (apparently you calculate it by subtracting the "New Task Start Time" for the "Old Task Start Time").

Your trigger is correct, and after you capture the start time of the new task, you will need to access the previous item of your SP List and use the dateDifference expression to calculate the difference between the 2 "start times". If your List keeps a chronological order (e.g.: the 10th item will necessarily have a starter time later than the 9th and sooner than the 11st), you will be able to capture the "old task" item by just subtracting 1 from the ID of the new item.

Before we move forward, I would like to ask you the following questions about your process:

Does your list keep a chronological order, as mentioned above?
Does your list receives tasks from different days? If yes, how do you intend to calculate the time spent with the "last task of the day"?

Also, please let me know if the structure recommended above makes sense for you, and if you have any additional question so far.

mmamesc1b · ‎05-31-2023

Hi, @rzaneti!

Yes, I think this structure works!

1) Yes, the list is in chronological order

2) It only includes tasks from the same day

3) I have a task called "End of Day" that the user would enter when they leave for the day; this entry would be used to calculate the time for the last real task of the day.

Thank you!

mmamesc1b · ‎05-31-2023

[Duplicate reply removed]

rzaneti · ‎05-31-2023

Hi @mmamesc1b ,

Perfect! Check the steps below.

SharePoint List Structure

Step 1: Trigger/Get Items

I guess that you are already familiar with this part: you just have to trigger your flow from the insertion of a new item to the List and, after the trigger, run the 'Get Items' action (Note: 'Get Items', not 'Get Item'). No filters are needed for the 'Get Items' action.

Step 2: Get the Index of old task

Now we we need to get the ID of the task that was just finished, it means, the second-last item of your List. In the previous message, I mentioned that you could just take the ID of the last item and subtract 1 from that, but it is not accurate (if your users delete any record and entry it again, the logic will break).

So as an alternative, you will get the index from the second-last item of the 'Get Items' output and access the ID of it. To make the process clearer, I'm storing the index in a variable, and I use the following expression to get it: sub(length(outputs('Get_items')?['body/value']), 2)

The "sub()" expression works as a subtraction, and we are basically capturing the length of the 'Get items' array and subtracting 2 values from it (Power Automate arrays are zero-indexed, so they start from 0, 1, 2...).

Note: for mistake, I named the variable as "finished_task_id". However, it is not storing the task id, but only the task index 🙂

Step 3: Get the old task record

Now we need to isolate the old task from the array. To do it, you just need to pass the index between squared brackets after the 'Get Items' output, assigning this result to a "Compose" action. The compose action will basically creates a new object (in our case, will be just a clone of the object that represents the old record), and you will use this expression in the input:

outputs('Get_items')?['body/value'][variables('finished_task_id')]. Note that we are passing the index variable between [ ] after the output of the value from 'Get items' action.

Step 4: Calculate the time difference

Finally, we will be able to calculate the time difference between the old and the new task! I recommend you to store it in a variable (make sure to assign to a String variable, and to let the Data Type of your List column in SharePoint as single line of text), but you can add the formula directly to the 'Update item' action if you prefer.

You will use this expression as value assigned to the variable:

dateDifference(outputs('Compose')['Starttime'], triggerOutputs()?['body/Starttime']). Make sure to add the old task start time as first argument and the new task start time as second (otherwise, you will have a negative number as result).

Step 5 (final): Update the List

Then you can update your list record. Make sure to fill all fields from Compose output (it will overwrite the record, and if you leave it empty, the records will be replaced with blanks). To get the outputs from Compose, you will need to follow this pattern: outputs('Compose')['COLUMNAME'] (just replace COLUMN for your actual column name).

The result will appear in your List like this:

Let me know if it works or if you need anything else!!