cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Dual Super User III
Dual Super User III

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

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



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Highlighted
Frequent Visitor

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

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!

View solution in original post

7 REPLIES 7
Highlighted
Dual Super User III
Dual Super User III

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

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



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Highlighted
Frequent Visitor

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

Thank you for 

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:
 
5.jpg
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. 
Highlighted
Dual Super User III
Dual Super User III

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

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!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Frequent Visitor

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

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.

Highlighted
Frequent Visitor

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

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?

Highlighted
Frequent Visitor

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

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!

View solution in original post

Highlighted
Dual Super User III
Dual Super User III

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

So you found a workaround for the latest challenge?

Congrats! Great Job!

Thanx for making this community great!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

Announcements
Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Users online (5,708)