cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kazssym
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

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
v-yuazh-msft
Community Support
Community Support

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

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

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)".

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (4,478)