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])
)
Solved! Go to 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.
Below expression might serve the same with much shorter way
TEXT([Date],"DDDD, MMMM D, YYYY")
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.
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |