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

ISO 8601 date-time conversion with Excel Online (Business)?

I tried the new preview Excel Online (Business) connector in my Flow for adding a row with a date-time field to an Excel table. It did not convert ISO date-time strings to numbers as the classic Excel connector did. The values were from a 'Current time' action.

 

Is it a bug or an intentional change?

 

(I wrote this question first in the PowerApps Community by accident and this is a repost.)

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: ISO 8601 date-time conversion with Excel Online (Business)?

I finally resolved this problem by converting data-time strings to Excel date numbers using "@div(sub(ticks(body('Current_time')), ticks('1900-01-01T00:00:00Z')), 86400e+7)".

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: ISO 8601 date-time conversion with Excel Online (Business)?

Hi @ kazssym,

 

Would you please share a screenshot of the configuration of your flow?

Would you please explain more about converting ISO date strings as the classic Excel connector did that you mentioned?

Would you please share a screenshot about the different results for the connectors?

 

I have made a test on my side to insert a row with "Excel" connector and "Excel Online (Business)" connector, and insert the row with a column fill in the Output of the  "Current time" action, the column would always insert with the time formate such as "2018-04-04T02:22:18.4718732Z", the result is no difference between "Excel" connector and "Excel Online (Business)" connector.

 

Please share more details or screenshots so we could try to help you.

 

 

 

Regards,
Alice Zhang

Highlighted
Frequent Visitor

Re: ISO 8601 date-time conversion with Excel Online (Business)?

This is a minimal test case.

 

In the Excel table, the first row was inserted by the 'Excel' connector and the second was by the 'Excel Online (Business)' connector.  The A column's format is set to Date.

 

Flow with 'Excel' connectorFlow with 'Excel' connectorFlow with 'Excel Online (Business)' connectorFlow with 'Excel Online (Business)' connectorScreenshot-2018-4-4 flowtest xlsx.png

Highlighted
Frequent Visitor

Re: ISO 8601 date-time conversion with Excel Online (Business)?

I finally resolved this problem by converting data-time strings to Excel date numbers using "@div(sub(ticks(body('Current_time')), ticks('1900-01-01T00:00:00Z')), 86400e+7)".

View solution in original post

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (8,564)