How to format date but sort as normal date

[Jon_Capria]

Hi,

I have been struggling with sorting and grouping a DATE column after formatting it and perhaps there’s a workaround for it, I just need to find the right way to do it.

Let me explain:

• I have a DATE column that is saved with the regular formatting “12/31/2020” which I can group by or sort as I wish…

• Then, I created a DATE FORMATED virtual column so it looks like this “Thursday, December 31st, 2020”

So my problem is, if I wanted to group by ASC or DESC the DATE FORMATTED column, it doesn’t quite work the same as the regular date format.

How can I group by DATE FORMATTED so I can see it but keep the DATE order?

Any suggestions and help are going to be appreciated,

Thanks.

PSD.
If you were wondering how I formatted the date here it is:

CONCATENATE(
SWITCH(WEEKDAY([DATE]),
1, Sunday,
2, Monday,
3, Tuesday,
4, Wednesday,
5, Thursday,
6, Friday,
7, Saturday,
“Invalid date!”),
", ",
SWITCH(MONTH([DATE]),
1, January,
2, February,
3, March,
4, April,
5, May,
6, June,
7, July,
8, August,
9, September,
10, October,
11, November,
12, December,
“Invalid date!”),
" ",
CONCATENATE(
DAY([DATE]),
IF(
IN(
DAY([DATE]),
LIST(11, 12, 13)
),
“th”,
SWITCH(
RIGHT(DAY([DATE]), 1),
“1”, “st”,
“2”, “nd”,
“3”, “rd”,
“th”
)
)
),
", ",
YEAR([DATE])
)

[LeventK]

Below expression might serve the same with much shorter way

TEXT([Date],"DDDD, MMMM D, YYYY")

[Jon_Capria · Accepted solution]

Thank you, I knew there was a way to format it like that, I just hadn’t found the right article.

Now, that simplifies the formatting process but I know it won’t solve the sorting problem using it on the “Data Validation > Valid If” or the “Auto Compute > App Formula” Sections because they would just convert the value to text.

There’s another solution I hadn’t notice before, and I’m pretty ok with leaving it like this for now… On the “Type Details” section, I turned on the toggle that says “Use long date format” the result is Dec 12, 2020, and THIS SORTS AND GROUPS AS EXPECTED…

Anyways, eventually, I would like to know how to format and sort like my question just to have more control of it but for now, I’m happy with this.

Thank you for your feedback, time and support.