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
Solved! Go to 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.
Don't sort by the concatenated text, sort by the date column (without displaying it).
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
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.
Thanks to both of you @Marc_Dillon & @SkrOYC
I gonna run with the date format that you suggested.
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
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 ?
Format the first part in ISO standard way yyyy-mm-dd
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:
Yes.
No.
Thanks,
it works but it's a little confusing (at least for italian users that use d/m/y).
Any workaround do you suggest ?
That was the workaround.
😂 😂 😂
Thanks 👏
User | Count |
---|---|
38 | |
32 | |
30 | |
17 | |
16 |