cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SteMatu
Regular Visitor

Linking a collection to an excel-table - date column not importing

Hi everyone,

i have linked a collection to an excel-table using  "ClearCollect(colPrice;pricingtable)".

Every single column is linked very well, except the date column and i cant figure out the problem. 

I tried different formatting for the date in excel but the collumn of my collection stays empty.

Importing the date as a number works, but it shows up the wrong date in a gallery, it's alway 1970-01-01.

 

any suggestions?

sorry for my poor english 😑

1 ACCEPTED SOLUTION

Accepted Solutions
v-jefferni
Community Support
Community Support

Hi @SteMat ,

 

So, if you are able to use a new column with the Text formulas to convert the previous column date values, it is the solution.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

4 REPLIES 4
Nogueira1306
Super User
Super User

I would say for you to create the collection with Patch().

 

Try this:

 

ForAll(

Patch(

colPrice,

Defaults(colPrice){

...

ColDate: DateValue(DateExcel)

...

 

}

 

That way you are forcing the item to be a Date

If that does not work, try converting in excel.. set it field as text

 

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

Last Post on Community

v-jefferni
Community Support
Community Support

Hi @SteMatu ,

 

If you have formatted the column to Text, then fill up those cells with date form texts, you will be able to retrieve all the date column values into Power Apps collection and gallery, no matter how you format this column in Excel:

vjefferni_0-1637738572913.png

vjefferni_1-1637738621555.png

 

But you should note that, if you change this column to Date or other formats, any new entries will not be able to display in Power Apps correctly, basically be blank all the time.

 

So, the best approach to make power apps with Excel table as the data source is, use Text columns all over the table and never change.

 

Hope this helps.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Not sure what you mentioned me to do, but it gave me a hint for a solution.

I created another column in excel with the formula =TEXT(Eingabemaske'!O17;"dd.mm.yyyy") which is readable for powerapps.

 

v-jefferni
Community Support
Community Support

Hi @SteMat ,

 

So, if you are able to use a new column with the Text formulas to convert the previous column date values, it is the solution.

 

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,282)