web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / Calculate time diff fr...
Power Automate
Unanswered

Calculate time diff from activities and sum up km

(0) ShareShare
ReportReport
Posted on by kristof 67
Dear PowerAddicts,

My Array of data:

[
  {
    "start-2022-10-28": "2022-10-28T18:20:28Z",
    "end-2022-10-28": "2022-10-28T18:41:05Z",
    "km-2022-10-28": 6.38
  },
  {
    "start-2022-10-28": "2022-10-28T08:14:07Z",
    "end-2022-10-28": "2022-10-28T08:31:24Z",
    "km-2022-10-28": 6.5
  },
  {
    "start-2022-10-27": "2022-10-27T18:53:00Z",
    "end-2022-10-27": "2022-10-27T19:12:08Z",
    "km-2022-10-27": 6.44
  },
  {
    "start-2022-10-27": "2022-10-27T07:49:31Z",
    "end-2022-10-27": "2022-10-27T08:07:22Z",
    "km-2022-10-27": 6.5
  },
  {
    "start-2022-10-26": "2022-10-26T18:25:30Z",
    "end-2022-10-26": "2022-10-26T18:46:33Z",
    "km-2022-10-26": 7.23
  },
  {
    "start-2022-10-26": "2022-10-26T08:02:55Z",
    "end-2022-10-26": "2022-10-26T08:22:05Z",
    "km-2022-10-26": 6.49
  },
  {
    "start-2022-10-25": "2022-10-25T19:01:41Z",
    "end-2022-10-25": "2022-10-25T19:23:13Z",
    "km-2022-10-25": 6.52
  },
  {
    "start-2022-10-25": "2022-10-25T07:57:26Z",
    "end-2022-10-25": "2022-10-25T08:17:08Z",
    "km-2022-10-25": 6.43
  },
  {
    "start-2022-10-24": "2022-10-24T17:58:26Z",
    "end-2022-10-24": "2022-10-24T18:19:46Z",
    "km-2022-10-24": 7.3
  },
  {
    "start-2022-10-24": "2022-10-24T08:10:58Z",
    "end-2022-10-24": "2022-10-24T08:30:21Z",
    "km-2022-10-24": 6.44
  },
  {
    "start-2022-10-21": "2022-10-21T18:12:14Z",
    "end-2022-10-21": "2022-10-21T18:32:03Z",
    "km-2022-10-21": 6.57
  },
  {
    "start-2022-10-21": "2022-10-21T07:57:05Z",
    "end-2022-10-21": "2022-10-21T08:15:19Z",
    "km-2022-10-21": 6.43
  },
  {
    "start-2022-10-20": "2022-10-20T19:34:48Z",
    "end-2022-10-20": "2022-10-20T19:54:06Z",
    "km-2022-10-20": 6.05
  },
  {
    "start-2022-10-20": "2022-10-20T18:20:43Z",
    "end-2022-10-20": "2022-10-20T18:27:45Z",
    "km-2022-10-20": 0.54
  },
  {
    "start-2022-10-20": "2022-10-20T08:05:53Z",
    "end-2022-10-20": "2022-10-20T08:25:28Z",
    "km-2022-10-20": 6.53
  },
  {
    "start-2022-10-19": "2022-10-19T18:49:16Z",
    "end-2022-10-19": "2022-10-19T19:06:16Z",
    "km-2022-10-19": 6.59
  },
  {
    "start-2022-10-19": "2022-10-19T07:44:41Z",
    "end-2022-10-19": "2022-10-19T08:07:22Z",
    "km-2022-10-19": 6.44
  },
  {
    "start-2022-10-18": "2022-10-18T18:58:37Z",
    "end-2022-10-18": "2022-10-18T19:21:19Z",
    "km-2022-10-18": 6.52
  },
  {
    "start-2022-10-18": "2022-10-18T07:59:32Z",
    "end-2022-10-18": "2022-10-18T08:17:46Z",
    "km-2022-10-18": 6.39
  },
  {
    "start-2022-10-17": "2022-10-17T18:17:38Z",
    "end-2022-10-17": "2022-10-17T18:39:11Z",
    "km-2022-10-17": 6.59
  },
  {
    "start-2022-10-17": "2022-10-17T08:05:57Z",
    "end-2022-10-17": "2022-10-17T08:25:09Z",
    "km-2022-10-17": 6.47
  },
  {
    "start-2022-10-14": "2022-10-14T18:05:54Z",
    "end-2022-10-14": "2022-10-14T18:25:08Z",
    "km-2022-10-14": 6.63
  },
  {
    "start-2022-10-14": "2022-10-14T08:19:27Z",
    "end-2022-10-14": "2022-10-14T08:37:45Z",
    "km-2022-10-14": 6.46
  },
  {
    "start-2022-10-13": "2022-10-13T19:42:23Z",
    "end-2022-10-13": "2022-10-13T20:03:37Z",
    "km-2022-10-13": 6.65
  },
  {
    "start-2022-10-13": "2022-10-13T07:44:56Z",
    "end-2022-10-13": "2022-10-13T08:03:44Z",
    "km-2022-10-13": 6.44
  },
  {
    "start-2022-10-12": "2022-10-12T18:45:17Z",
    "end-2022-10-12": "2022-10-12T19:10:22Z",
    "km-2022-10-12": 7.48
  },
  {
    "start-2022-10-12": "2022-10-12T13:03:47Z",
    "end-2022-10-12": "2022-10-12T13:18:40Z",
    "km-2022-10-12": 2.17
  },
  {
    "start-2022-10-12": "2022-10-12T11:11:39Z",
    "end-2022-10-12": "2022-10-12T11:21:34Z",
    "km-2022-10-12": 2.39
  },
  {
    "start-2022-10-12": "2022-10-12T08:08:47Z",
    "end-2022-10-12": "2022-10-12T08:13:27Z",
    "km-2022-10-12": 6.56
  },
  {
    "start-2022-10-11": "2022-10-11T18:18:51Z",
    "end-2022-10-11": "2022-10-11T18:39:31Z",
    "km-2022-10-11": 6.58
  },
  {
    "start-2022-10-11": "2022-10-11T07:24:56Z",
    "end-2022-10-11": "2022-10-11T07:45:59Z",
    "km-2022-10-11": 6.4
  },
  {
    "start-2022-10-10": "2022-10-10T18:27:17Z",
    "end-2022-10-10": "2022-10-10T18:50:28Z",
    "km-2022-10-10": 6.41
  },
  {
    "start-2022-10-10": "2022-10-10T08:20:06Z",
    "end-2022-10-10": "2022-10-10T08:39:46Z",
    "km-2022-10-10": 6.48
  },
  {
    "start-2022-10-07": "2022-10-07T18:17:07Z",
    "end-2022-10-07": "2022-10-07T18:41:21Z",
    "km-2022-10-07": 6.55
  },
  {
    "start-2022-10-07": "2022-10-07T08:00:57Z",
    "end-2022-10-07": "2022-10-07T08:22:07Z",
    "km-2022-10-07": 6.45
  },
  {
    "start-2022-10-06": "2022-10-06T18:56:23Z",
    "end-2022-10-06": "2022-10-06T19:17:29Z",
    "km-2022-10-06": 6.57
  },
  {
    "start-2022-10-06": "2022-10-06T07:47:12Z",
    "end-2022-10-06": "2022-10-06T08:08:18Z",
    "km-2022-10-06": 6.5
  },
  {
    "start-2022-10-05": "2022-10-05T18:26:32Z",
    "end-2022-10-05": "2022-10-05T18:47:42Z",
    "km-2022-10-05": 6.38
  },
  {
    "start-2022-10-05": "2022-10-05T08:02:43Z",
    "end-2022-10-05": "2022-10-05T08:21:43Z",
    "km-2022-10-05": 6.48
  },
  {
    "start-2022-10-04": "2022-10-04T18:10:22Z",
    "end-2022-10-04": "2022-10-04T18:33:32Z",
    "km-2022-10-04": 6.52
  },
  {
    "start-2022-10-04": "2022-10-04T07:46:23Z",
    "end-2022-10-04": "2022-10-04T08:06:21Z",
    "km-2022-10-04": 6.44
  },
  {
    "start-2022-10-03": "2022-10-03T18:26:58Z",
    "end-2022-10-03": "2022-10-03T18:49:30Z",
    "km-2022-10-03": 6.59
  },
  {
    "start-2022-10-03": "2022-10-03T08:03:15Z",
    "end-2022-10-03": "2022-10-03T08:26:25Z",
    "km-2022-10-03": 6.33
  }
]

My expected result is an array of daily join of time and distance defined as follows:

Hours at work: Time Difference in hours between the first(end) and last(start) date from that day.
Distance: Sum of km's from the same date

[{
"day": "2022-10-03",
"hours": 8.5,
"distance": 12.92},
{...}
]

thank you.
Categories:
Building flows
I have the same question (0)
  • grantjenkins Profile Picture
    grantjenkins 11,063 Moderator on at

    This was definitely a fun challenge. A lot trickier than first thought 🙂

     

    Not sure if the way I've done it is overly complicated but did my best. Below is a sample of what the output would be. Note that I split up the hours and minutes but could combine if you wanted.

    [
 {
 "day": "2022-10-28",
 "hours": 2,
 "minutes": 37,
 "distance": "12.88"
 },
 {
 "day": "2022-10-27",
 "hours": 0,
 "minutes": 36,
 "distance": "12.94"
 }
]

     

    The full flow is below. I'll go into each of the actions.

    grantjenkins_15-1667109504152.png

     

    The trigger in my example is just a manual input.

     

    The Data (Compose) is just a copy of your JSON input. I changed your first start date to 2 hours earlier to test the hours was working correctly.

    grantjenkins_16-1667109532600.png

     

     

    The tricky part with this input was the property names all being different (appended date at the end of start, end and km). I used an Excel script to remove the date part leaving just start, end, and km as the property names for all objects.

    grantjenkins_17-1667109567518.png

     

    The Office script I used is below. Essentially, it's a replace method that allows you to pass in regex.

    function main(
 workbook: ExcelScript.Workbook,
 searchString: string,
 regexPattern: string,
 regexFlags: string,
 replaceString?: string): string {
 if (typeof (replaceString) === 'undefined') {
 replaceString = '';
 }

 let re = new RegExp(regexPattern, regexFlags)

 return searchString.replace(re, replaceString)
}

     

    The regex I used to match the properties is:

    (start|end|km)-(\d{4})-(\d{2})-(\d{2})

     

    Effectively it looks for a string starting with either start, end, or km, followed by a hyphen, 4 digits, a hyphen, 2 digits, a hyphen and 2 more digits. I have (start|end|km) as a group so I can replace everything with whatever it matched with those options. So, if it found the string started with end then the entire match would be replaced with end, etc. The $1 in replaceString means get the option that it found (start, end, or km).

     

    I then used Parse JSON Script to parse the results with the appropriate schema.

    grantjenkins_18-1667109695187.png

     

    And the schema:

    {
 "type": "array",
 "items": {
 "type": "object",
 "properties": {
 "start": {
 "type": "string"
 },
 "end": {
 "type": "string"
 },
 "km": {
 "type": "number"
 }
 },
 "required": [
 "start",
 "end",
 "km"
 ]
 }
}

     

    Next, Select Script is used to get the appropriate properties and values (day, km, ticks). Ticks is the difference between the two dates that we can use to sum the totals for each day, then divide to get the hours and minutes.

    grantjenkins_19-1667109741327.png

     

    The expression for day is:

    formatDateTime(item()?['start'], 'yyyy-MM-dd')

     

    And the expression for ticks is:

    sub(ticks(item()?['end']), ticks(item()?['start']))

     

    Sample output of the Select is:

    [
 {
 "day": "2022-10-28",
 "km": 6.38,
 "ticks": 84370000000
 },
 {
 "day": "2022-10-28",
 "km": 6.5,
 "ticks": 10370000000
 },
 {
 "day": "2022-10-27",
 "km": 6.44,
 "ticks": 11480000000
 }
]

     

    I then initialize a few variables.

    days is an array that will end up holding all the unique days (distinct) that we can loop through.
    output is an array that will contain the final output.
    ticks is an integer that will be used to add the ticks for each day. Initial value is 0.
    km is a float that will be used to add the kms for each day. Initial value is 0.

    grantjenkins_20-1667109853320.png

     

    Next, I use an Apply to each to get a full list of the days from Select Script and append to the days array.

    grantjenkins_21-1667109875746.png

     

    I then use Days (Compose) to remove duplicates using the following expression.

    union(variables('days'),variables('days'))

    grantjenkins_22-1667109916514.png

     

    We then have Apply to each day that iterates over each of the unique days. and for each day we sum up the ticks and kms.

    grantjenkins_23-1667109942429.png

     

    The Filter array filters our Select Script array so only items with the current day are used.

    grantjenkins_24-1667109961587.png

     

    Compose ticks adds the current value in the variable ticks with the ticks in the current item.

    Compose km adds the current value in the variable km with the km in the current item.

     

    Then we take the total from the Compose actions and set them as the new values for the variables ticks and km. The expressions for both of these are:

    add(variables('ticks'), item()?['ticks'])
add(variables('km'), item()?['km'])

    grantjenkins_25-1667110015627.png

     

    The next step is to add all the values for the current day into an object and append it to the output array variable.

    grantjenkins_26-1667110042877.png

     

    To get the total number of hours we divide the number of ticks by 36000000000.

    To get the total number of minutes (excluding the hours) we divide the number of ticks by 

    600000000, and get the mod (remainder) of the result divided by 60).
    We already have the total distance but want to format the result, so it only shows 2 decimal places.
     
    Expressions for each of these are below:

    div(variables('ticks'), 36000000000)
mod(div(variables('ticks'), 600000000), 60)
formatNumber(variables('km'),'0.00')

     

    Finally, we reset the variables ticks and km to 0 ready for the next day totals.

    grantjenkins_27-1667110094946.png

     

    I added a Compose right at the end so we can see the contents of our variable output.

    grantjenkins_28-1667110110003.png

     

    Easy peasy 😕

     

    Any questions, please don't hesitate to ask.

  • kristof Profile Picture
    kristof 67 on at

    Hi @grantjenkins Appreciate your effort, my challenge is to calculate those ticks between daily activities as follows:

    You calculated the diff of activities: 

    sub(ticks(item()?['end']), ticks(item()?['start']))

     My goal is to loop over those activities and get the difference between the first ['end'] and the last ['start'] in hours. 
    Screenshot 2022-10-30 110513.png

  • Verified answer
    grantjenkins Profile Picture
    grantjenkins 11,063 Moderator on at

    Ok - have modified it to get that output. See changed actions below.

     

    Select Script now includes start and end, and removed ticks.

    grantjenkins_0-1667129137827.png

     

    Just after the Filter array (inside the Apply to each day) I added a Set variable ticks with the following expression:

    sub(ticks(body('Filter_array')[0]?['start']),ticks(body('Filter_array')[sub(length(body('Filter_array')),1)]?['end']))

    This gets the ticks for the first start and the last end and subtracts them to get the duration.

    grantjenkins_1-1667129270416.png

     

    In the Apply to each day item I removed Compose ticks and Set variable ticks.

    grantjenkins_2-1667129393039.png

     

    And lastly, I removed Set variable ticks to 0.

     

    Done some initial testing and seems to be getting the correct results. Let me know if any issues.

  • Verified answer
    kristof Profile Picture
    kristof 67 on at

    Thank @grantjenkins I just figured out meanwhile with a simple apply to each. Screenshot 2022-10-30 165600.png
    I tried dateDifference instead of ticking and sum up the daily distance by xpath 

      Hours: dateDifference(last(body('Select_end_by_day')),first(body('Select_start_by_day')))
      Distance: FormatNumber(xpath(xml(outputs('JSON')), 'sum(/root/Numbers)'),'0.00')

    Thanks a lot. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 522 Super User 2025 Season 2

#2
Tomac Profile Picture

Tomac 364 Moderator

#3
abm abm Profile Picture

abm abm 243 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Share your ideas and struggles with building Power Automate flows!
Survey: Power Automate mobile app
How to Ask for Help with Your Power Automate Workflow
Flows are not visible for users who sign in with their personal accounts
Start and wait for an approval action
Restore a deleted flow
List of actions cheat sheet for Power Automate Desktop
Question for everyone : How are you using Create Text GPT in AI Builder?
Welcome to the Power Automate forum!

Product updates

Microsoft Power Platform Community release plans