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

Date Sorting as Number Rather Than Date

I have a sort button that sorts by a column called "date_different_format". Because of data type issues between Excel and PowerApps, the data is coming in from Excel as text, which means that the formula below sorts it as text rather than as a date. 

 

I have a general inkling that I need to cram a DateValue() into the formula somewhere, but I cannot find a variation that doesn't end in lots of angry red and white x's replacing all my gallery content.  Can anyone help?  @Shanescows ?

 

SortByColumns(Search([@time_logs_1], EntrySearchBox.Text, "date_different_format", "project_id", "staff_person"), "date_different_format", If(SortDescending1, Ascending, Descending))

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @justtegan ,

Do you want to sort your Gallery Items based on the "date_different_format" acted as Date value rather than Text value?

 

Based on the issue that you mentioned, I think the DateValue property could achieve your needs. I have made a test on my side, please consider take a try with the following workaround:

Set the Items proeprty of the Gallery to following:

SortByColumns(
               Search(
                       AddColumns([@time_logs_1], "DateDifferentFormat_DateValue", DateValue(date_different_format)),
                       EntrySearchBox.Text, 
                       "date_different_format", "project_id", "staff_person"
               ), 
               "DateDifferentFormat_DateValue", 
               If(SortDescending1, Ascending, Descending)
)

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Dual Super User II
Dual Super User II

Hey @justtegan 

 

I carried out a test on my side and if the column is formatted as date as format "dd/mm/yyyy" in excel, then it sorts the results properly.

If this is a text type field. Can you try to update your expression as:

SortByColumns(Search([@time_logs_1], EntrySearchBox.Text, "date_different_format", "project_id", "staff_person"), Text(date_different_format,"yyyymmdd"), If(SortDescending1, Ascending, Descending))

 

Hope this Helps!

 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

This isn't working for me-- I THINK the reason it isn't working is that I had to wrap date_different_format in a DateValue() function wherever it is displayed in the app to get around a reformatting issue that was happening with some versions of iOS.

Community Support
Community Support

Hi @justtegan ,

Do you want to sort your Gallery Items based on the "date_different_format" acted as Date value rather than Text value?

 

Based on the issue that you mentioned, I think the DateValue property could achieve your needs. I have made a test on my side, please consider take a try with the following workaround:

Set the Items proeprty of the Gallery to following:

SortByColumns(
               Search(
                       AddColumns([@time_logs_1], "DateDifferentFormat_DateValue", DateValue(date_different_format)),
                       EntrySearchBox.Text, 
                       "date_different_format", "project_id", "staff_person"
               ), 
               "DateDifferentFormat_DateValue", 
               If(SortDescending1, Ascending, Descending)
)

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

@v-xida-msft This does work, thank you. I had figured out a work around by adding another data card to the form (which I made invisible), then feeding the datepicker selected date into it as a different format, then using that invisible card value to sort the gallery.

 

Your solution is much more elegant, and I would like to better understand how it works. It looks to me like your formula adds a temp column within the search function named "datedifferentformat_datevalue" and uses DateValue() to reformat the date_different_format column value as a date, then feeds the result into the temp column. Is that correct? 

 

UPDATE: @v-xida-msft Coming back 24 hours later because I realized that, while this did fix the sorting problem, it caused another problem in other areas of the app. Somehow, changing the gallery items formula to yours caused a disconnect between the data source and the entry and edit forms.  All the elements that reference the selected value from the gallery screen are throwing errors. I'm thinking this is because wrapping the data source reference up in the AddColumns() function is somehow interrupting record selection.  Any ideas?

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (19,850)