Sort dates that are in a text column

[eddie61]

Hi all

I like to display dates to my users in the following way (and the code that I use in the virtual column):

CONCATENATE(
  INDEX(
   LIST(
    "So", "Mo", "Di", "Mi", "Do", "Fr", "Sa"
   ),
  WEEKDAY([datum])
 ),
 ", ",TEXT([datum],"dd.mm.yyyy")
)

The problem is that it is not sorting correctly (by date), as this new virtual column needs to be a "text" column to display the day with the date combination.

Is there a solution or workaround to get the desired, or similar result?

Thanks in advance.

Adrian

[Marc_Dillon]

Don't sort by the concatenated text, sort by the date column (without displaying it).

[SkrOYC]

Problem is that he uses this text column as a group, where you just get the option to sort by ascending or descending using that value

[Marc_Dillon · Accepted solution]

Haha, you're right. I did actually notice that, then started writing a response, then thought about something else and completely forgot about the grouping, then erased what I first wrote to post the above message. 

But yah, @eddie61 , with that custom concatenation you're doing, and with the grouping, I see no way you'll be able to sort like you want. I always recommend using ISO date format, mainly for this exact reason. That is: yyyy-mm-dd. Feel free to add in your day abbreviations at the end of the ISO-formatted date, though.

[eddie61]

Thanks to both of you @Marc_Dillon & @SkrOYC 

I gonna run with the date format that you suggested.

[SkrOYC]

Yep! That's the one I use for my document generation also, it's so easy to sort

I also generally make groups by month using something like "2022-07 July"

A little bit ugly for sure but useful

[FaCe]

Hi all,

i'm trying to do something similar.

I'd like to group for date and showing that in this format:

i've used in group this virtual table:

CONCATENATE (TEXT([DATA], "d/m/yyyy"), " - ", [VTGiornoSettimana], " ", TEXT([DATA], "d"), " ", [VTMese], " ", TEXT([DATA], "yyyy"))

And

VTMese is another virtual column:

SWITCH(month([DATA]),

"1","gennaio",

"2","febbraio",

"3","marzo",

"4","aprile",

"5","maggio",

"6","giugno",

"7","luglio",

"8","agosto",

"9","settembre",

"10","ottobre",

"11","novembre",

"12","dicembre",

"")

and VTGiornoSettimana is another virtual column:

SWITCH(weekday([DATA]),

"1","Domenica",

"2","Lunedì",

"3","Martedì",

"4","Mercoledì",

"5","Giovedì",

"6","Venerdì",

"7","Sabato",

"")

so that my actual output is:

But even with the date (converted in text by virtual column) not all dates are sorted properly.

How can i solve it ? 

[Marc_Dillon]

Format the first part in ISO standard way yyyy-mm-dd

[FaCe]

Hi Marc,

in this way ?

CONCATENATE (TEXT([DATA], "yyyy-mm-dd")

No way to hide then it (the first part) when group by so that i can see only the last part:

[Marc_Dillon]

Yes.

No.

[FaCe]

Thanks,

it works but it's a little confusing (at least for italian users that use d/m/y).

Any workaround do you suggest ?

[Marc_Dillon]

That was the workaround.

[FaCe]

😂 😂 😂

Thanks 👏