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

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

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

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

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
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

secondImage

Demo Extravaganza is Back!

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

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (35,705)