cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leo85
Regular Visitor

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

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

 

leo85_0-1618435681083.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
leo85
Regular Visitor

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

View solution in original post

4 REPLIES 4
v-xiaochen-msft
Community Support
Community Support

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.

v-xiaochen-msft_0-1618454414550.png

v-xiaochen-msft_1-1618454429701.png

v-xiaochen-msft_2-1618454448484.png

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.

 

 

Yes, the column is formated as a date with the respective format. There are no other formulas.

leo85_0-1618467744790.png

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.

v-xiaochen-msft
Community Support
Community Support

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.

leo85
Regular Visitor

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

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

Top Solution Authors
Top Kudoed Authors
Users online (69,131)