Answered

Calculating the Difference of 'Created time' and 'Start time' in Power Automate

Like (0) Share

Report

Posted on 4 Sep 2023 07:02:49 by jennamarudo

I'm working on a Power Automate flow where I need to calculate the time difference between two datetime values from a CSV file attached to an Outlook email. The CSV data includes columns for "Created time" and "Start time." I want to create a flow that extracts these datetime values and calculates the time difference between them. My date columns are Created time and Start time,
The sample value:
Created time: 9/4/2023 12:06:56 AM

Start time: 9/4/2023 12:02:46 AM

Producing an output in the format "0:04:10."

I'm seeking assistance in configuring this flow effectively. Thank you

All responses (10)

Answers (1)

Chriddle 7,520 Super User 2025 Season 1 on 11 Sep 2023 at 07:40:58

Like (0)

Report
Re: Calculating the Difference of 'Created time' and 'Start time' in Power Automate

Since the date difference function returns a string that is not easy to do calculations on, use the ticks function with the dates like @v-qiaqi-msft mentioned:

https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#ticks

Dividing the returned value by 600000000 gives the difference in minutes.

Of course, you can adjust this divider to get the unit of time you want.

Just change the value for "Diff" in the Select action to this:

div( sub( ticks( parseDateTime(split(item(), ',')[1]), parseDateTime(split(item(), ',')[2]) ), ), 600000000 )
jennamarudo 41 on 11 Sep 2023 at 06:47:20

Like (0)

Report

Re: Calculating the Difference of 'Created time' and 'Start time' in Power Automate

Thank you, @Chriddle , for your assistance, apologize for the delay in my response. Here's an update on my flow: I've successfully calculated the DateDifference, but I'm encountering an error while trying to compute the average of DateDifference values.
I've attached a screenshot of my flow and the expression I've been using.
Verified answer

Chriddle 7,520 Super User 2025 Season 1 on 07 Sep 2023 at 08:28:00

Like (1)

Report
Re: Calculating the Difference of 'Created time' and 'Start time' in Power Automate

Ok, if this date format comes from a CSV, you have to convert it.

parseDateTime( outputs('Split_Each_Item')[1] )

This is how I would build it (simplyfied 😉).

It avoids the loop, which slows down the flow and saves some actions, making it easier to keep track of.

Here the CSV data is in a Compose action

Name,Created,Start Jim,2023-08-13 00:01:12 ,2023-09-02 01:00:00 Jim,2023-08-14 00:01:12 ,2023-09-03 01:00:00 Jim,2023-08-15 00:01:12 ,2023-09-04 01:00:00

Select

From:

skip( split(outputs('Compose'), decodeUriComponent('%0A')), 1 )

Map

Name:

split(item(), ',')[0]

Created:

parseDateTime(split(item(), ',')[1])

Start:

parseDateTime(split(item(), ',')[2])

Diff:

dateDifference( parseDateTime(split(item(), ',')[1]), parseDateTime(split(item(), ',')[2]) )

Output

[ { "Name": "Jim", "Created": "2023-08-13T00:01:12.0000000", "Start": "2023-09-02T01:00:00.0000000", "Diff": "20.00:58:48" }, { "Name": "Jim", "Created": "2023-08-14T00:01:12.0000000", "Start": "2023-09-03T01:00:00.0000000", "Diff": "20.00:58:48" }, { "Name": "Jim", "Created": "2023-08-15T00:01:12.0000000", "Start": "2023-09-04T01:00:00.0000000", "Diff": "20.00:58:48" } ]

'
jennamarudo 41 on 07 Sep 2023 at 07:45:29

Like (0)

Report

Re: Calculating the Difference of 'Created time' and 'Start time' in Power Automate

Hello @Chriddle ,
In my previous step, I obtained the values for 'Create' and 'Start' times. I've also attached a screenshot to provide a visual reference for how I'm trying to retrieve these values.
Chriddle 7,520 Super User 2025 Season 1 on 07 Sep 2023 at 07:30:53

Like (0)

Report
Re: Calculating the Difference of 'Created time' and 'Start time' in Power Automate

Have you ever formated these date values in your flow? If so, simply don't do that 😉

If this date format is from an external data source, use parseDateTime() function to get a valid date format:

parseDateTime('2023-08-13 00:01:12')
jennamarudo 41 on 07 Sep 2023 at 07:25:20

Like (0)

Report

Re: Calculating the Difference of 'Created time' and 'Start time' in Power Automate

"I've resolved the previous issue, but now I've encountered a new error:
Error Message: InvalidTemplate. Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'In function 'dateDifference,' the value provided for date time string '"2023-08-13 00:01:12"' was not valid. The datetime string must match ISO 8601 format.
Can anyone please provide guidance on how to format the datetime string correctly to meet the ISO 8601 format? Your assistance would be greatly appreciated. Thank you!"
Chriddle 7,520 Super User 2025 Season 1 on 07 Sep 2023 at 07:12:14

Like (0)

Report
Re: Calculating the Difference of 'Created time' and 'Start time' in Power Automate

First you should solve the problem with the extra quotes in your date values (this might have happened earlier in your flow).

After that you can add a third row to the Select's map called e.g. "Diff", with the value expression:

dateDifference(Created, Start)

Replace "Created" and "Start" there with the expression you have used in the rows above to calculate these values.
jennamarudo 41 on 07 Sep 2023 at 02:11:17

Like (0)

Report

Re: Calculating the Difference of 'Created time' and 'Start time' in Power Automate

Hello, I have the results for the 'create time' and 'start time' arrays. Could anyone assist me in calculating the time difference between them? Your help is highly appreciated! Thank you!
Chriddle 7,520 Super User 2025 Season 1 on 04 Sep 2023 at 07:54:03

Like (0)

Report
Re: Calculating the Difference of 'Created time' and 'Start time' in Power Automate

You can use the dateDifference function:

dateDifference('9/4/2023 12:02:46 AM', '9/4/2023 12:06:56 AM')

This expression returns '00:04:10'
v-qiaqi@microsoft.com on 04 Sep 2023 at 07:09:27

Like (1)

Report
Re: Calculating the Difference of 'Created time' and 'Start time' in Power Automate

Hi @jennamarudo,

Please try as below:

div(sub(ticks(outputs('Start Date and Time')),ticks(outputs('End Date and Time')),600000000)