Dates from excel not sorting properly

(0) Share

Report

Posted on by Moranox

Hi for some reason the dates are not showing properly. I've updated the data format using

DateValue(DateCompleted,"en-GB")

But it is still not showing properly. Any ideas?

Attached a screenshot...

many thanks

Categories:

Building Power Apps

All responses (10)

Answers (0)

Sort by

Rick72 385 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Moranox,

Could you also show what is configured in the property "Items" of the gallery and if it is using a collection show how the collection is created. That way we can understand your situation better.

Rick
Moranox 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Rick,

Please see below.

SortByColumns(Search(DailyCheck, TextSearchBox1.Text, "DateCompleted"), "DateCompleted", If(SortDescending1, Descending, Ascending))

Many thanks for your help.

Hans
Rick72 385 on at

Like
a
(0)

Report
Copy link

Link copied!
Just for the check-double-check:

"DailyCheck" is a SharePoint list?

The date shown in the gallery is the column "DateCompleted"?
Moranox 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi

"DailyCheck" is a Sheet in an Excel document saved on Business Onedrive.
"DateCompleted" is the item I've been trying to sort by date in the gallery.

Thank you

Hans
Rick72 385 on at

Like
a
(0)

Report
Copy link

Link copied!

O bummer. I overlooked the title.

So still for the check double-check: The date shown in the gallery is the column "DateCompleted"?
Moranox 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

Yes this is correct.

Thanks

Ha s
Rick72 385 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Hans,

I just tried to reproduce your situation (easy version).

I have an Excel file in OneDrive. It has a table with 2 columns: Product and Date. When I create an app based on this table, and I update the formula for the property "Items" for the gallery control so it sort on date, it sorts on the date just fine.

I suspect DateCompleted in Excel is not of type "Date". Is this true?

Rick
Moranox 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi

This is correct. I cannot change it in table mode.

Thank

H
Rick72 385 on at

Like
a
(0)

Report
Copy link

Link copied!

Ok. Than is that your problem I am afraid. "Fix" Excel and sorting will be possible.

I do not know how many items are in the Excel table, but perhaps - I let that testing up to you - it is also possible to create a collection using the SortByColumns function and the AddColumns function and the DateValue function on the Excel table. This collection is then used the property "Items".

ClearCollect(newTable, SortByColumns(AddColumns(DailyCheck, "newDateCompleted", DateValue(DateCompleted)), "newDateCompleted")

Please share your results with us.

Rick
Moranox 10 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Rick,

Hope you are well. Apologies for my late reply. Unfortunately, solution you proposed did not work as expected. However, you mentioned changing the format to date in your earlier message and that was the problem. I've managed to do it and it resolved the problem.

The solution
Change the Format to Date in the Excel Document using Table Query window, not by using the format dropdown from the Home Ribbon.

Many thanks for your help.

Kind regards

Hans