Date powerapps to excel | Just date, not time

[Tomjoh92]

When i send a date from datepicker to excel, it also sends time.
I just want the
"23.02.2018"

, not "23.02:2018 00:00"

Ive tried with Text(DatePicker1.SelectedDate; "dd.mm.yyyy"), but still get the time. Any suggestions?

It also withdraws one day from the selected date when sent to excel. If i select 23:02.2018, i get 22.02.2018 in excel.

[mr-dang]

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.

[tmalone]

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]

@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 🙂

[tchin-nin]

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

Click on New / List

Set the name and click Create

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

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

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

Théo

[tmalone]

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

[aprildunnam]

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]

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

[aprildunnam]

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]

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.

[aprildunnam]

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