cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akharns
Level 8

Excel Data Type - date vs string

I have a PowerApp that displays on-prem Excel data in a Data Table.  I'm not clear on how it determines data types.  I have used one column in the Excel document to show elapsed time, a string (not date) in the format "dd hh:mm".  It is formatted as text in Excel.  I have made a few changes to my app and it now sees it as a date, with an incorrect value in the date portion.  I can't seem to find a way to get it to interpret the column as a simple string, so it will present correctly.

 

After doing some reading, it looks like I could have computed the elapsed time in PowerApps, which maybe I'll try someday.  A quick try led to the error I am seeing right now.  Now I need to find a way to roll it back to the previous behavior that was working at one time.

 

Any help will be appreciated.

2 REPLIES 2
Super User
Super User

Re: Excel Data Type - date vs string

Hi @akharns 

The easiest way to do this is to make a copy of your Excel data.

Using the spreadsheet that's connected to your PowerApp, clear the data in the elapsed time column. Enter some dummy text data into the cells in the elapsed time column. 

Next, refresh the Excel data source from the PowerApps designer. The app should now recognise the elapsed time column as a text field. Finally, return to your source spreadsheet and paste your orginal data back into the elapsed time column.

That should hopefully do the trick for you.

 

akharns
Level 8

Re: Excel Data Type - date vs string

I haven't gotten this to work yet.  I copied the data to a column not in the table and then cleared the data and refreshed a few times.  It finally recognized the field as cleared.  Then I copied text back in and it is formatted as dates, as before.  Next, I cleared the data and got it to refresh.  I then duplicated the column, naming the new column to the old column name, and putting ABC and DEF in all rows for the respective columns.  So far I haven't gotten the app to see either of those two values after many many refreshes.

 

To refresh, I am going to View/Data sources in the web app and selecting Refresh for the data source. 

 

I have also tried several aother actions such as closing the Excel document and closing and reopening the browser.

 

Maybe I need to entirely delete the column, refresh to get an error, and then re-add it?

 

After this is resolved, I'll be interested in knowing how to avoid this problem in the future.

Helpful resources

Announcements
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

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

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