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

[kazssym]

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

[v-yuazh-msft]

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

[kazssym]

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 Online (Business)' connector

[kazssym · Accepted solution]

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