Answered

Importing Dates/Time from Excel into a Sharepoint List using Power Automate

(0) Share

Report

Posted on by Asirbu

I'm hoping somebody can shed some light on this issue. I am trying to build a flow that adds new entries/updates existing entries in a SP list based on an Excel Spreadsheet stored on OneDrive. The spreadsheet contains both columns with dates and date & time. I've researched this extensively, trying to get the dates properly transferred into SP. The common solution is to use an expression AddDays (for dates) and AddSeconds (for time), as per tutorials here and here.

However, these methods require the dates and time to be passed as numerical values (integers and float respectively) from Excel. It appears that the Excel Online connector only passes those values as string. As discussed in this thread, this used to work with the standard Excel connector, but this connector got deprecated. I'm hoping somebody has a workaround or solution. Thank you!

Categories:

Using flows

I have the same question (0)

All responses (7)

Answers (2)

Verified answer

efialttes 14,756 on at

Like (1)

Report
Hi!

You should be able to convert a number in string format to int or float by means of int() or float() WDL expressions

If you need further assistance please let us know

It appears that the Excel Online connector only passes those values as string. So if your Excel column is of tipe Date (no Time) you can try with the following

addDayaddDays('1899–12–30T00:00:00Z',int(item()?['TestingDate']))

Just replace item()?['TestingDate'] with the coresponding dynamic content that represents your date column.

Also this excelent article discusses DateTime translation

Another approach is, some people prefer to switch their Excel column format from date into text

Hope this helps

Was this reply helpful? Yes No
Asirbu 24 on at

Like (0)

Report

Thank you for @efialttes
I managed to get the flow to run successfully. But the time is still inconsistent:
I'm using this expression to get the time value to pass in SP.:

addSeconds('1899-12-30',int(formatNumber(mul(variables('EndTimeFloat'),86400),'0','en-us')),'yyyy-MM-dd H:mm')

Here's the Excel sheet, and the outputs in SP:

I've also tried to use the TEXT function in Excel, to convert the time to text in the spreadsheet. Time values are still inconsistent upon import.

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (0)

Report

Hi again

In order to replicate the scenario, can you share the time raw values provided by your Excel action block? I mean, the 'List rows present in a table' column outputs before applying WDL expressions

Thanx!

Was this reply helpful? Yes No
Asirbu 24 on at

Like (0)

Report

For sure, @efialttes , please see below:

"Start Date":"44098","Start Time":"0.375","End Time": "0.625" --> come from Excel as string
Then, I used int and float expressions in Power Automate, as suggested, to convert the values to integers and float.
Since SP expects date & time in the time columns, I added the date value back to the float values for time, getting the following outputs for time:
Start Time: 44098.375
End Time: 44098.625
These are the values that are passed to this expression:
addSeconds('1899-12-30',int(formatNumber(mul(variables('StartTimeFloat'),86400),'0','en-us')),'yyyy-MM-dd H:mm') --> returning in the SP list for start time 9/24/2020 2:00 AM (instead of the expected 9/24/2020 9:00 AM)
addSeconds('1899-12-30',int(formatNumber(mul(variables('StartTimeFloat'),86400),'0','en-us')),'yyyy-MM-dd H:mm') --> returning in the SP list for end time 9/24/2020 8:00 AM (instead of the expected 9/24/2020 3:00 PM)

Thank you so much for looking into this.

Was this reply helpful? Yes No
Asirbu 24 on at

Like (0)

Report

Could it be a time zone thing? I'm noticing that the returned time is exactly 7 hours less than what is expected, both for Start Time and End Time?

Was this reply helpful? Yes No
Verified answer

Asirbu 24 on at

Like (2)

Report

I'm not sure what causes the 7 hours discrepancy, but got it to work by adding the time. This is the final expression:
addSeconds('1899-12-30',add(int(formatNumber(mul(variables('StartTimeFloat'),86400),'0','en-us')),25200),'yyyy-MM-dd H:mm')

Thank you everyone!

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (0)

Report

So you found a workaround for the latest challenge?

Congrats! Great Job!

Thanx for making this community great!

Was this reply helpful? Yes No