cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JC75
Level: Powered On

Date and time format issues - excel and data tables

I have a form that connects to excel.

 

on the edit form there is a date picker(formatted short date) , this saves to an excel column also formatted as date only. However on submit it reformats it to date and time(and sometimes the wrong format .ie. yyyy-mm-dd hh:mm:ss when I just want it to remain as a simple DD-mm-yy.

 

i then have a data table in the form - which also showing this excel sheet, the date column here however shows a date and time - how do I ensure that the date submitted to excel remains date only, and the time only, or that the data table equally only shows date and no time,

 

is is it a setting in PowerApps and/or excel to make this work correctly? I don’t want a time value stored as this is collected separate in another column but I keep this at the moment to only a text input, due to same issue of it also wanting to then put a date with it(which mean the column in the data table would show date also when it should be a column with date and a col7mn with time.

 

im using uk language and defaults( I think PowerApps as several things that need improving on how dates and times are used both in display and how the back end processes them, as I regularly have issues doing calculations with dates because of it either reverting to US format or including times etc especially when using things like datevalue from text etc)

 

 

4 REPLIES 4
Community Support Team
Community Support Team

Re: Date and time format issues - excel and data tables

Hi JC75,

 

I am afraid that it might be a default behavior.

 

When we show date value on Excel table on an app, it will be shown as Date and time value by default. Then we can use Text function to format it to a Date only format.

 

While when we try to save Date only format from the app to Excel table, it will be saved as Date and time value, though we have formatted the selected date as Short date.

 

About the data table control, currently data in Data table control is read-only and we don’t have a way to customize the styling of individual columns.


Best regards,
Mabel Mao

 

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JC75
Level: Powered On

Re: Date and time format issues - excel and data tables

Ok thank you for the information and reaching out.

 

However I think there a bunch of issues that need to be addressed then with various datetime functions in powerapps and connections.

 

especially with the first issue(PA sending both Date-Time) vs the last(dataTable) as it this results in there being no solution to just display the date, as the text function cant be used there

 

UTC format cant be just time or date only either - it would be useful that it could be say shortdate.utc as the function and equally there are several issues with UTC resulting in wrong times/dates when using Today or Now functions(these issues been highlighted elsewhere on these forums)

 

A lot of this seems related to locale settings and the various functions not taking into account these such as dateadd etc which are working in US formats. if datevalue is used from a textbox and dateadd etc will often end up with incorrect results because of the varying locale settings.

es2
Level: Power Up

Re: Date and time format issues - excel and data tables

Another thing to add on to this post: I had the same problem and fixed it with the text function. However, another problem has arisen-Even though the app is set to submit the date in short format and this holds on some of the app users devices, but one user in particular makes an entry into the app, and, portrayed is the date in long format. Could it be his device settings since other devices (users) hold this short date format?

pinarrehber
Level: Powered On

Re: Date and time format issues - excel and data tables

Hi 

@v-yamao-msft

 

On my app I deleted the time part of date picker so users only chose date and submitted and it would reflect to my excel as such "09.08.2018 21:00". 

But right now I dont know what happend on excel i see the values as ########## as dates and as text they are something like 43227875. 

How can I change this to date format. So many people have entered these values and i really need to fix them. 

 

Thank you in advance,