Date field corrupt in Excel

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

I have an app connected to an Excel file with one field as date.

When testing in the web it works well. I was able to create, retrieve and edit a few records without issues.

However, when I tested the app in a cellular, it corrupted the date field in all the existing records.

If I see the date column as a general number, it's clear that the issue is that somewhere the dates lost the commas (in my locale the decimal separator is the comma), causing corrupt data for a date:

The correct value for today would be 43140.125, and not 43140125.

Does anybody know if this is a bug? Is there any workaround?

Categories:

Building Power Apps

All responses (12)

Answers (1)

Sort by

v-monli-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous,

What did you mean by "cellular"? Did you mean the PowerApps Windows studio?

Regards,

Mona
Meneghino 6,949 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi Andres, I would not use Excel as a data source...
https://baizini-it.com/blog/index.php/2017/08/29/powerapps-101-data-storage/
Sienna 1,532 on at

Like
a
(2)

Report
Copy link

Link copied!

@Meneghino
I agree that excel isn’t very good chice as datasource but most of the company uses excel as datasource for many reason. One of them is easy to work with. Excel had been used for many years and is already so implemented that for some task excel is the only one choice. Advantage of it is for example if something would go wrong with PA then anyone can access the data without any problem and use it appart from Azure which would be very complicated. Just one question.. where did you get those information from? Is it just your personal opinion ?
Meneghino 6,949 on at

Like
a
(3)

Report
Copy link

Link copied!

Hi @Sienna, thanks for following up with your comments.

I agree that Excel is easy to work with, and I use it all the time for reporting and many other tasks. It is unbeatable for many uses.
However, for storing structured data that needs to be accessed by other applications, then it is my personal opinion that the disadvantages always outweigh the advantages in almost any scenario where there is a valid alternative. If there is no alternative, then please use Excel, but the fun and the learning will be limited.

PS Just to clarify that my personal opinion is based on helping a number of people transition from experimenting with using Excel as a data source in PowerApps to using SP/CDS/Azure. No-one has ever regretted the move.
Sienna 1,532 on at

Like
a
(1)

Report
Copy link

Link copied!

@Meneghino
Once again I agree about the Excel and the Sharepoint however since powerapps is not for personal use anymore then people have to use whatever company agree to. I personally like MySql but can't use it due to company rules. I haven't tried azure yet and honestly due to some reasons which I don't want to talk about here I don't really want to. But once again.. fully agree with you
Verified answer

timl 37,287 Super User 2026 Season 2 on at

Like
a
(2)

Report
Copy link

Link copied!

Whilst I agree that Excel isn't a great choice, I also have sympathy for @Anonymous After all, he has fallen victim to a bug that has cost him both time and data loss.

@v-monli-msftI assume that cellular means mobile phone. I guess the date corruption problem occurs only on the mobile player, and not the web player.

If Excel is the only choice, one way to work around this problem is to store the date and time values in yyyymmddhhmm format, as @Meneghino mentions in his post here:

https://powerusers.microsoft.com/t5/General-Discussion/Please-help-with-UTC/m-p/21244/highlight/true#M9183

Another benefit of this method is that it would provide some protection against the timezone bug.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I mean the mobile phone app.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Another good reason is price. In order to use the DB connectors, MS requires to upgrade the suscription. And if you have hundreds or thousands of users who can deal with the basic suscription, it's much too expensive. Instead I would invest time, effort and money in deploying my own webapp/mobile app synched with some standard db that will be free and with much better performance.
Meneghino 6,949 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous, you mention that "In order to use the DB connectors, MS requires to upgrade the suscription"
Is that the case? I am using Azure SQL DB as a back end without any upgrade in subscription for my users.
Could you please clarify in what scenario you need to upgrade?

PS I am paying ~$5/month for Azure SQL Database in its most basic configuration
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Meneghino, which connector are you using? The "SQL Server" or any other specific for Azure?