Answered

Calculating Wait Time from Excel to SharePoint

(0) Share

Report

Posted on by Phineas

5,331 Moderator

Finally headed in the right direction.

I've gotten the 'InTime' and 'OutTime' to move through Power Automate and populate the new SharePoint list with the correct data.

Now, I need the difference between the two to be displayed in the 'WaitTime' column of the SharePoint list.

I have a formula in the Excel 'WaitTime' column that is calculating the difference in Excel.

I'm now wondering if the Excel formula is adversely impacting the accuracy of any expression I write on the Power Automate
side.

What is the best practice for calculating and displaying the 'WaitTime' between 'InTime' and 'OutTime' in the Power Automate
flow and into SharePoint?

The 'Create item' 'WaitTime' formula is -

dateDifference('1899-12-30',items('Apply_to_each')?['WaitTime'])

Categories:

Building flows

I have the same question (0)

All responses (15)

Answers (1)

Sort by

Sundeep_Malik 6,501 on at

Like (0)

Report
Copy link

Link copied!

Hey @Phineas

In your excel can you do it hh:mm:ss.

Then in your power automate you can write an expression like this:

formatDateTime(WaitTimeExpressionFromExcel,'hh:mm:ss')

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

Skip

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

As suggested, I placed the following in the 'WaitTime' field of the 'Create item' -
dateDifference('1899-12-30',items('Apply_to_each')?['WaitTime'])

I updated the Excel 'WaitTime' column with hh:mm:ss

The result is still an error about the string/datetime value, which is a decimal value in the 'Create item' rather than the time value shown in Excel.

Was this reply helpful? Yes No
Sundeep_Malik 6,501 on at

Like (0)

Report
Copy link

Link copied!

@Phineas

Can you show the flow in edit mode, the expression you used there.

I told to use this expression:

formatDateTime(WaitTimeExpressionFromExcel,'hh:mm:ss')

Not this:

dateDifference('1899-12-30',items('Apply_to_each')?['WaitTime'])

Edited: Use this in your waitTime Text Box:

formatDateTime(items('Apply_to_each')?['WaitTime'],'hh:mm:ss')

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

I apologize.

I am unsure where to place your formula. Does it go in the 'WaitTime' field of the 'Create item' shown below?

formatDateTime(WaitTimeExpressionFromExcel,'hh:mm:ss')

Does the blue content above stay as it is or do I overwrite it with something local?

Was this reply helpful? Yes No
Sundeep_Malik 6,501 on at

Like (0)

Report
Copy link

Link copied!

@Phineas

Yes put it there in WaitTime:

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

Nope, Power Automate doesn't like it.

Was this reply helpful? Yes No
Sundeep_Malik 6,501 on at

Like (0)

Report
Copy link

Link copied!

@Phineas

Add this:

formatDateTime(items('Apply_to_each')?['WaitTime'],'hh:mm:ss')

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

Getting close. Power Automate took the formula; There is output to the SharePoint list; However, it is not accurate and not displayed in time hh:mm:ss.

Was this reply helpful? Yes No
Sundeep_Malik 6,501 on at

Like (0)

Report
Copy link

Link copied!

Pretty strange, because the formula I submitted should only show hours, minutes and seconds.

Can you directly add the wait time action in create item once and see what output you are getting.

Just add this:

items('Apply_to_each')?['WaitTime']

Let's see what output that gives

Was this reply helpful? Yes No