Answered

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

(0) Share

Report

Posted on 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

Categories:

General topics

I have the same question (0)

All responses (10)

Answers (1)

v-qiaqi@microsoft.com on at

Like (1)

Report
Hi @jennamarudo,

Please try as below:

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

Was this reply helpful? Yes No
Chriddle 8,443 Super User 2025 Season 2 on at

Like (0)

Report
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'

Was this reply helpful? Yes No
jennamarudo 41 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
Chriddle 8,443 Super User 2025 Season 2 on at

Like (0)

Report
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.

Was this reply helpful? Yes No
jennamarudo 41 on at

Like (0)

Report

"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!"

Was this reply helpful? Yes No
Chriddle 8,443 Super User 2025 Season 2 on at

Like (0)

Report
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')

Was this reply helpful? Yes No
jennamarudo 41 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Chriddle 8,443 Super User 2025 Season 2 on at

Like (1)

Report
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" } ]

'

Was this reply helpful? Yes No
jennamarudo 41 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Chriddle 8,443 Super User 2025 Season 2 on at

Like (0)

Report
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 )

Was this reply helpful? Yes No