cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
German_Chris
Frequent Visitor

Date Picker save in Excel strange format

Hello,

I have some problems with a PA connected to a Excel-Table.
I use a Form with a Datepicker. In the Update-Field I only added a DateAdd()-Function to the standard/automatic-Function which is created for this datacard. By the way, the following "issue" is also without the DateAdd()-Function.

 

DateAdd(DateValue2.SelectedDate + Time(Value(HourValue2.Selected.Value); Value(MinuteValue2.Selected.Value); 0); -TimeZoneOffset() ; Minutes)

If I submit the Form in Excel does not show the Datetime as selected. 
In Excel is a DateTime-TimeZone String created, something like this TEXT:  2021-04-07T09:00:00.000Z
This format I could not use in Excel as date.
I need somthing like this: 07.04.2021 9:00
The Excel cell are formated as "TT.MM.JJJJ hh:mm", I tried other formats, nothing helped.
Best regards
ChrisDatepicker_Problems.png


2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @German_Chris,

I could tell you that a General column is the only way to solve your problem currently. There is something with your form configuration.

Do you mean that if you use a General column to store the date and time, you could not use the Date picker in Power Apps?

That's not the big deal, you could delete the original Text input control within the data card and replace it with a Date Picker and a Hour drop down, a Minute drop down. 

Here is what I test in my scenario.

Delete the original Text Input and insert a Date Picker and a Hour drop down, a Minute drop down. 

Set the Hour drop down Items property:

["00","01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23"]

Set the Hour drop down Default property:

Text(Hour(DateTimeValue(ThisItem.Date)),"[$-en-US]00")

Set the Minute drop down Items:

["00","01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34","35","36","37","38","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59"]

Set the Minute drop down Default property:

Text(Minute(DateTimeValue(ThisItem.Date)),"[$-en-US]00")

Please check the GIF as below.

DateTime.gif

Regards,

Qi

Best Regards,
Qi

View solution in original post

German_Chris
Frequent Visitor

@v-qiaqi-msft 
Many thanks for your respond. After a lot of try I found the problem.
I used the Excel-Online-Connector, with this one the Datepicker has an problem. I changed it to One-Drive-Business Connector and this solved the problem....
Best regards

Chris

View solution in original post

6 REPLIES 6
v-qiaqi-msft
Community Support
Community Support

Hi @German_Chris,

Based on the issue that you mentioned, do you want to submit date and time to Excel table?

Could you please share a bit more about the scenario?

Actually, I recommend that you should create a General column to store the date and time, just give up any other formatted column type.

A normal Text type date and time could achieve your needs.

Set the Update property of the data card as below:

 

DatePicker1.SelectedDate + Time(Value(HourDropdown.Selected.Value), Value(MinutesDropdown.Selected.Value), 0)

 

2021-04-08-01.png

Hope it could help.

Regards,

Qi

Best Regards,
Qi

No this dies not solve the problem. 😞

Best regards 

Chris 

Hi @German_Chris,

Sorry for the late reply, just come back from my vocation.

Have you solved your problem?

Could you please tell me that why my idea could not solve your problem, is there anything error that you could share with me?

Please provide more details about your scenario or tell me what is going on with my solution.

Regards,

Qi

Best Regards,
Qi

Hello @v-qiaqi-msft ,
my Problem is the same as above mentioned.
If I save a datetime in the PA, in the Excel-Table always shows the datetimezone (ISO-somthing) not a readable format. And clear formate dates in Excel doesn't show in the PA.
I tried a lot of formats in Excel for the date-column, nothing helped (General, Custom, Date, Time).

If I change the format the column to general, PA identify the column as text and I could not use the datepicker in the form.

PA_DatetimeProblems.png

Best regards
Chris





  

Hi @German_Chris,

I could tell you that a General column is the only way to solve your problem currently. There is something with your form configuration.

Do you mean that if you use a General column to store the date and time, you could not use the Date picker in Power Apps?

That's not the big deal, you could delete the original Text input control within the data card and replace it with a Date Picker and a Hour drop down, a Minute drop down. 

Here is what I test in my scenario.

Delete the original Text Input and insert a Date Picker and a Hour drop down, a Minute drop down. 

Set the Hour drop down Items property:

["00","01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23"]

Set the Hour drop down Default property:

Text(Hour(DateTimeValue(ThisItem.Date)),"[$-en-US]00")

Set the Minute drop down Items:

["00","01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34","35","36","37","38","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59"]

Set the Minute drop down Default property:

Text(Minute(DateTimeValue(ThisItem.Date)),"[$-en-US]00")

Please check the GIF as below.

DateTime.gif

Regards,

Qi

Best Regards,
Qi

View solution in original post

German_Chris
Frequent Visitor

@v-qiaqi-msft 
Many thanks for your respond. After a lot of try I found the problem.
I used the Excel-Online-Connector, with this one the Datepicker has an problem. I changed it to One-Drive-Business Connector and this solved the problem....
Best regards

Chris

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

Users online (1,110)