cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mr-dang
Level 10

Re: Date powerapps to excel | Just date, not time

The problem is very straight forward here: PowerApps needs a datasource in which its columns are of one type of data: all numbers, all text, all dates, etc.

 

But in a spreadsheet, data can take all kinds of forms in the same column. In Column A, I could have a string, a number, true/false. I can create validation so that certain cells must conform to its rules. But it's not the level of validation you would find in a real database.

 

When you use SharePoint, Common Data Service, or SQL, you determine the type of data each column takes: an Id column will strictly take integers, a Name field will only take 256 charcters of text.

 

So somewhere during your use of your Excel spreadsheet, you had interacted with it in ways that made it inconsistent. Using the Text() function around the date to format it in the way you want makes it text. But if you put that text into the same column that previously contained datetimes, it makes the contents incompatible.

 

PowerApps treats the data from sources like Excel and Sheets as text. Because of this, it is not an ideal source when you need data to fit another format. Spreadsheets are fast and excellent for prototyping, but I would encourage you to build your production apps against SharePoint, the Common Data Service, or SQL.

 

Otherwise, you will need to use DateValue() around every instance of dates appearing from the table so that you can convert dates stored as text back into dates.

Microsoft Employee
@8bitclassroom
tmalone
Level: Powered On

Re: Date powerapps to excel | Just date, not time

One set of date Pickers doesn't use DateValue() and yet it seems to be logging data into excel with out times (sometimes). To possibly help narrow down the problem I have a date picker with this information in the data pane under advanced.

 

DefaultDate:

Text(Today()-1,"[$-en-US]mm/dd/yyyy")

 

DateTimeZone:

DateTimeZone.UTC

 

Format:

Text(Today()-1,"[$-en-US]mm/dd/yyyy")

 

Please excuse any redundancy as I've gotten as far as I could with just trial and error and sourcing from online. 

 

Unless I'm mistaken I'm not sure this statement applies to my app since I'm not trying to pull data from excel but rather just store it there. "PowerApps treats the data from sources like Excel and Sheets as text." I appreciate your time and helping me with my ignorance on the subject. 

 

I did spend a bit of time trying to figure out how to use sharepoint to do the same thing but I couldn't figure out how to make a list.

 

Thanks for your time!

TMalone

mr-dang
Level 10

Re: Date powerapps to excel | Just date, not time

@tmalone, it is good that you have access to SharePoint. We have many experts like @darogael @Shanescows @aprildunnam who come from the SharePoint world and can point you to their learning resources at the intersection of PowerApps x SharePoint. You'll find it will achieve much more than Excel.

 

I'd like to give a shoutout to other experts with SharePoint from our community like @Drrickryp@tchin-nin.

 

You're in good company Smiley Happy

Microsoft Employee
@8bitclassroom
tchin-nin
Level 10

Re: Date powerapps to excel | Just date, not time

Hi @tmalone

 

To create a list on SharePoint, you need to have access to a site with at least Member role -or Contribute permission-.

Then you can follow the following screenshots :

Click on the Engine icon, then Site Content

Capture0.PNG

 

Click on New / List

Capture1.PNG

Set the name and click Create

Capture2.PNG

Now that your list is created you need to add your columns : click on the Engine / List Settings

Capture3.PNG

Then you have all the default columns, you can add new one

Capture4.PNG

Enter the name of you column and select the type. For DateTime you can choose whether you want Date and Time or Date OnlyCapture5.PNG

 

Théo

 

tmalone
Level: Powered On

Re: Date powerapps to excel | Just date, not time

This is perfect! Thanks so much for providing this to me. I have a question, if I have multiple locations that all use the same set of data, i.e. they all produce widget x how do I distinguish the different locations ? In excel I had a sheet for each location that contained only that type of widget. 

 

Thanks,

 

-TMalone

Super User
Super User

Re: Date powerapps to excel | Just date, not time

How many total locations are we talking about? There are two ways in SharePoint to accomplish this: 1) Create a Choice Column and populate that with your locations so that you can assign widget x to that location. 2) Create a separate SharePoint List called "Locations" to hold your list of locations and in your widgets list create what's called a "Lookup Column" to that Locations list.  I generally suggest using the seperate list/lookup column approach if you have more than a handful of items.

tmalone
Level: Powered On

Re: Date powerapps to excel | Just date, not time

The second idea sounds perfect. I have three locations, however I might need to do it inverted where each location does a look up for widget x, widget y, and widget z. 

 

basically this:

 

Location 1 Produced

     product x: type 1, type 2, type 3, and so on

     product y: type 1, type 2, type 3, and so on

     product z: type 1, type 2, type 3, and so on

 

Location 2 Produced

     product x: type 1, type 2, type 3, and so on

     product y: type 1, type 2, type 3, and so on

     product z: type 1, type 2, type 3, and so on

 

Location 3 Produced

     product x: type 1, type 2, type 3, and so on

     product y: type 1, type 2, type 3, and so on

     product z: type 1, type 2, type 3, and so on

 

Is this still possible? I apologize is the formating is confusing. I appreciate your help.

 

-TMalone

Super User
Super User

Re: Date powerapps to excel | Just date, not time

If I'm understanding, you have Products and each of those Products can have multiple "Types"?  And you are wanting to relate which product types are associated with a particular location?

tmalone
Level: Powered On

Re: Date powerapps to excel | Just date, not time

That is correct, every location can make a particular product that has different types ( like small, medium, and large). I want the app to log what each location produces. I managed to do this with excel by having multiple sheets that were associated with each location. So a sheet would look like "location1productx" and "location1producty" then the columns in a each sheet would have the the different types of product.

Highlighted
Super User
Super User

Re: Date powerapps to excel | Just date, not time

Ok here's what I would suggest:

 

1.  Create a Products list

2.  In the Products list, add a Choice field with the 3 locations listed as option

3.  You can tag each product in the Products list to an associated location

4.  In PowerApps, you can group by Location to show the products that each location produces