Date format in Excel change automatically

[Anonymous]

Greetings, I'm using Powerapps as a followup tools for quotes. I have somes dates picker field for "Required date", "Send date", "Accepted date" and I use a Gallery to sort them by those dates.

Data are stored in an Excel Sheet in Onedrive for business.

By default, Date Picker use the following format "aaaa-mm-jj hh:mm" in Excel. My Gallery recognize this format, and I use "Text(ThisItem.Date_x0020_Requis;"[$-fr-FR]dd mmmm yyyy")" to manipulate it and compare with today to send warning.

I got an issue where suddenly, my forms and gallery get empty, not recognizing data in dates fields. I open the Excel to discover that every dates are now in standard format, so display a number like 466631,644.

I have to select every column and set them back to "short date", or to "aaaa-mm-jj hh:mm", and it works for a day or two.

I don't know what triggers this, if you experience this isssue please comment.

My Excel software is in french but powerapps is in english, I don't know if this could be related.

Edit 1 : The Update field of the Date Picker is "DateValue14.SelectedDate + Time(Value(HourValue14.Selected.Value); Value(MinuteValue14.Selected.Value); 0)". Maybe by writing in text form to the Excel Sheet it changes it to Standard automatically. I'll maybe try to set different column for the hour:minute, and let the update field in Date format.

Thank you.

[v-micsh-msft]

Hi

Edit 1 : The Update field of the Date Picker is "DateValue14.SelectedDate + Time(Value(HourValue14.Selected.Value); Value(MinuteValue14.Selected.Value); 0)". 
Maybe by writing in text form to the Excel Sheet it changes it to Standard automatically. 
I'll maybe try to set different column for the hour:minute, and let the update field in Date format.

What is your current situation?

Per my testing, Every date field update from PowerApps would then reset the date format within Excel to custom type. 

Formatting the string with the Update property of the date field datacard will not help in such a situation.

I will make some further tests and let you know the results. Please also share your current situation if it is convenient.

Regards,

Michael

[cb4]

I have the exact same issue. It's started happening since the upgrade to Excel O365, not sure it that's related.

[Anonymous]

I use Excel 365 too and yesterday the IT support showed me I had updates to do on MS office 365, because Excel was crashing all the time. By going in File, Account and selecting Update options at the right.

[Anonymous]

Update : I didn't change the Update field, but for the last week I didn't had any problem. I think since the MS Office 365 update. So for now I'll consider this as resolved.

[Anonymous]

Just to follow up, the problem is not solved. Either dates format themself to standard, or a Number type column used to sort quote's # switched itself to standard type in Excel, then Powerapps can no longer recognize them.

I tried separated date and time column. I tried changing the update field to Text(DateValue2.SelectedDate;"dd/mm/yyyy") or to Text(DateValue2.SelectedDate;"shortdate"), same problem. 

I've rebuild the app completely. Same problem. 

I think what triggered this is whatever is coded in Update field, Powerapps writes in UTC format with T05:00:00.000Z at the end. Excel switch to standard because doesn't recognize that. other data stored without T05:00:00.000Z displays then somethings like 466631,644.

That doesn't explain the number column switching to standard. I've build a completely new app with no data, but you can see in the printscreen that after some times, Power apps doesn't recognize the data anymore because Number are expected.

[sx-vvoisin]

Hi @Anonymous ,

Have you been able to figure out your issue with date column format changing in your excel file ?

I have the same issue in a common context (Office365, Excel in OneDrive, we are in Quebec (French Canada)). 

I posted also on the community but it seems that the PowerApps support guys doesn't help furthermore after their first suggestion and when the problem is too complicated for them...

[Anonymous]

Hi, also in Quebec here. Sadly I gave up on using Powerapps because it wasn't working and no solution has been found.

The real solution is to not use Excel as a database. Since my company doesn't have SQL or Azure or anything else I'm stuck with Excel.

Here is a post explaining different Excel alternative. The poster recommend to avoid using Excel since it is not designed to be a database.

http://baizini-it.com/blog/index.php/2017/08/29/powerapps-101-data-storage/

Sorry I'm not of any help and hope you will find a solution.

[sx-vvoisin]

Vive les TI du Québec 🙂

I even tried to protect the format of the excel column with a password but PowerApps is too strong by altering the date format while i specify in the App how i want to get the user input.

I am exploring also the sharepoint list solution to store the data but run into challenges with formula delegation when using lookup or filter to get some records from the sharepoint list.

Unfortunately cannot go neither with SQL database on Azure or Common Data Service.

I keep investigating on this case as the functionnality and operation offered within the App is really amazing and it is riduculous that i cannot go in production because of a column format altering after a while.

[mmura]

I hace the same issue.
Please comment if anyone has a reached a solution.
Regards