Answered

Calculating Wait Time from Excel to SharePoint

(0) Share

Report

Posted on by Phineas

4,941 Super User 2025 Season 1

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

All responses (15)

Answers (1)

Phineas 4,941 Super User 2025 Season 1 on at

Like (1)

Report

Re: Calculating Wait Time from Excel to SharePoint

...and the first option only returns the date. I guess I'll just have to translate the 'WaitTime' in Power BI, Power Apps, or whatever UI will be used.

Thank you for your time and attention to this matter, you have been very helpful.
Verified answer

Sundeep_Malik 6,480 on at

Like (0)

Report

Re: Calculating Wait Time from Excel to SharePoint

@Phineas

I tried the second option but it didnt work, so the first option is the only way.
Sundeep_Malik 6,480 on at

Like (0)

Report

Re: Calculating Wait Time from Excel to SharePoint

@Phineas

Yea I wanted to see that only. So, I just checked, there is no time column in Sharepoint.

Both date and time column is there.

You can try one of the two things:

1) Change data type of the column to single line of text.

or

2) Use calculated column and only show the time: =TEXT([first column name],"hh:mm:ss")
Phineas 4,941 Super User 2025 Season 1 on at

Like (0)

Report

Re: Calculating Wait Time from Excel to SharePoint

Just to confirm your assertion, my compose DOES show hh:mm:ss.
Why it's translating the way it is to SP, I don't know.
Phineas 4,941 Super User 2025 Season 1 on at

Like (0)

Report

Re: Calculating Wait Time from Excel to SharePoint

Just so you can see how the 'WaitTime' column is formatted in SP.

Using the 'WaitTime' from Excel returned about the same data, minus twelve hours.
Sundeep_Malik 6,480 on at

Like (0)

Report

Re: Calculating Wait Time from Excel to SharePoint

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
Phineas 4,941 Super User 2025 Season 1 on at

Like (0)

Report

Re: Calculating Wait Time from Excel to SharePoint

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.
Sundeep_Malik 6,480 on at

Like (0)

Report

Re: Calculating Wait Time from Excel to SharePoint

@Phineas

Add this:

formatDateTime(items('Apply_to_each')?['WaitTime'],'hh:mm:ss')
Phineas 4,941 Super User 2025 Season 1 on at

Like (0)

Report

Re: Calculating Wait Time from Excel to SharePoint

Nope, Power Automate doesn't like it.
Sundeep_Malik 6,480 on at

Like (0)

Report

Re: Calculating Wait Time from Excel to SharePoint

@Phineas

Yes put it there in WaitTime: