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
kazssym
Frequent Visitor

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

kazssym
Frequent Visitor

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
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,924)