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

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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 😑

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Nogueira1306 7,390 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

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:

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.

Was this reply helpful? Yes No
SM-20081418-0 54 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No