Cannot format date correctly (yyyy-mm-dd) in Excel table with form

(0) Share

Report

Posted on by leo85

609

Hi,

i'm trying to create a new item in an Excel table with power apps. I have a simple form that is connected to the Excel table.

I have a date column in the Excel table, which is formated as a date (yyyy-mm-dd). The format that I want is "yyyy-mm-dd".

I have the date input field value in PowerApps set to Text(Today(),"[$-en-US]yyyy-mm-dd","de").

In the app the date is displayed correctly, e.g. (2021-04-14), but when I sumbit the form and the the new row is added to the Excel table the dateformat is off. there are two mistakes:

1. It displays not only the date, but also the time e.g. 2021-04-14T22:00:00.000Z

2. It subtracts 2 hours from the actual date, which leads to the date being one day off.

Can anybody help?

Regards,

Leo

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

v-xiaochen-msft on at

Like (0)

Report

Hi @leo85 ,

This seems to be a setting issue of excel rather than a powerapps issue.
I did a test for you and did not encounter your problem.
Have you used formulas or modified data types in excel?

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

Was this reply helpful? Yes No
leo85 609 on at

Like (0)

Report

Yes, the column is formated as a date with the respective format. There are no other formulas.
When I enter the same string (e.g. 2021-04-14) directly in Excel it works fine.
What I don't understand is that Power Apps seems to send this ISO-Formatted string, although a different format (yyyy-mm-dd) is specified in the app.

Was this reply helpful? Yes No
v-xiaochen-msft on at

Like (0)

Report

Hi @leo85 ,

I will suggest you use 'General' instead of 'Date'.
This will ensure that your date format is not corrupted.

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

Was this reply helpful? Yes No
Verified answer

leo85 609 on at

Like (0)

Report

Ok, so this seems to be an API error of the Excel connector. Power Apps is apparently not able to send the string in a specified format, but always uses the ISO format. And Excel does not recognize the ISO format as a valid datetime input. Looks like a typical Microsoft issue: Great idea, mediocre implementation and then forgotten. 😞

Thank you for your help. So the solution is to:
- Delete the current date colum
- Create a new column formatted as "General"
- Refresh the datasource in Power Apps
- Redo the date data card to send the string you want

Was this reply helpful? Yes No