Issues with Date Format from Excel Online Connector

Like (0) Share

Report

Posted on 11 Nov 2024 13:38:31 by CU11111322-0

Hello,

I am currently creating a canvas app in PowerApps and having issues with Date which is in my Excel Sheet. My data connector is through Excel Online Business, so I am importing the Data from Excel. My Excel Sheet contains of Roominformation, where you have the roomname, floor, etc... And one point is the last edited date.

Whenever I am creating the Edit Form and I am selecting the Room I am getting the error ""The expected value '08.11.2024 11:41' must be a valid RFC 3339 format for 'date-time'. Allowed ISO 8601 formats: 'YYYY-MM-DDThh:mm', 'YYYY-MM-DDThh:mm±hh', 'YYYY-MM-DDThh:mm'.""

The "Last edited" column in Excel is formatted to Date "short".

I can edit the entry in PowerApps, save it and it would write the date with the time into the Excel row but whenever I want to read the date in PowerApps again it does not show up and I am receiving the error.

I read multiple questions in this forum but none of it worked.

Note that my environment is in German and using the time zone in Germany.

Thanks!

Categories:

Connecting to Data

Connectors

PowerApp_1.png

PowerApp_2.png

PowerApp_3.png

All responses (3)

Answers (0)

CU11111322-0 6 on 13 Nov 2024 at 06:24:54

Like (0)

Report

Issues with Date Format from Excel Online Connector

Okey so the dates are already in the "short" date style in Excel. Should I keep them as it is in Excel and just adjust it with the DateValue and DateTimeValue functions in PowerApps and nothing in Excel?

If so where should I adjust it in PowerApps? It is an edit form and the "Last edit" is a DataCard in the Date Time Format in Power Apps. Should I adjust the Default Item for the Data Card or the DefaultDate for the DateValue? I am new to PowerApps thats why I ask more precisely.

Thanks!
Suggested answer

CU11111322-0 6 on 12 Nov 2024 at 05:44:40

Like (0)

Report

Issues with Date Format from Excel Online Connector

Okey so the dates are already in the "short" date style in Excel. Should I keep them as it is in Excel and just adjust it with the DateValue and DateTimeValue functions in PowerApps and nothing in Excel?

If so where should I adjust it in PowerApps? It is an edit form and the "Last edit" is a DataCard in the Date Time Format in Power Apps. Should I adjust the Default Item for the Data Card or the DefaultDate for the DateValue? I am new to PowerApps thats why I ask more precisely.

Thanks!
Suggested answer

jrletner 720 Super User 2025 Season 1 on 11 Nov 2024 at 19:19:55

Like (0)

Report

Issues with Date Format from Excel Online Connector

The error you’re experiencing might possibly be due to a mismatch between the date format in your Excel sheet (formatted in "short" date style, like "DD.MM.YYYY") and the ISO 8601 date-time format expected by Power Apps when using the Excel Online connector (which expects "YYYY-MM-DDThh:mm" or similar formats).

Here’s how to address this issue:

You can use DateValue and DateTimeValue functions in Power Apps to reformat the date correctly before using it in your app.

When reading the "Last edited" date field from Excel, use the DateValue or DateTimeValue function to ensure it’s formatted to the expected ISO 8601 format.

DateTimeValue( Text('Last edited', "[$-en-US]yyyy-MM-ddThh:mm:ssZ") )

Hope that works for you.