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

Date format in Excel change automatically

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.

8 REPLIES 8
Community Support Team
Community Support Team

Re: Date format in Excel change automatically

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

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

Re: Date format in Excel change automatically

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

christianfntn
Level: Powered On

Re: Date format in Excel change automatically

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.

christianfntn
Level: Powered On

Re: Date format in Excel change automatically

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.

Highlighted
christianfntn
Level: Powered On

Re: Date format in Excel change automatically

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
Level: Powered On

Re: Date format in Excel change automatically

Hi @christianfntn ,

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

christianfntn
Level: Powered On

Re: Date format in Excel change automatically

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
Level: Powered On

Re: Date format in Excel change automatically

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.

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,433)