I am having trouble putting my dates into a month (1/2/2019 ----->>> January). Then sorting the list by month order (Not in alphabetical order). I created a virtual column “Month”. I used this to switch the date format to a month:
SWITCH(
MONTH(TODAY()),
1, “January”,
2, “February”,
3, “March”,
4, “April”,
5, “May”,
6, “June”,
7, “July”,
8, “August”,
9, “September”,
10, “October”,
11, “November”,
12, “December”,
“”
)
This did not allow me to sort in order per month.
I then tried this: (on my virtual column and the actual date column)
INDEX(
LIST(
“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”,
“Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”
),
MONTH(TODAY())
)
Still will not sort in month order.
Any ideas?
Thanks in advance
Solved! Go to Solution.
For the virtual column, you’ll need to prefix the month name with a number that will sort as desired:
CONCATENATE(
MONTH([invoice_date]),
"-",
SWITCH(
MONTH([invoice_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”,
“”
)
)
or:
CONCATENATE(
MONTH([invoice_date]),
"-",
INDEX(
LIST(
“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”,
“Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”
),
MONTH([invoice_date])
)
)
For the virtual column, you’ll need to prefix the month name with a number that will sort as desired:
CONCATENATE(
MONTH([invoice_date]),
"-",
SWITCH(
MONTH([invoice_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”,
“”
)
)
or:
CONCATENATE(
MONTH([invoice_date]),
"-",
INDEX(
LIST(
“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”,
“Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”
),
MONTH([invoice_date])
)
)
Ok. And use the virtual column to do this?
Neither worked. All it does is put everything under - October?
Also, am i suppose to do something with the original column - “invoice_date” to index the date format?
Ah, yes, that’s important! I’ve updated the instructions above to use [invoice_date]
rather than TODAY()
. Try that.
You are the man! That worked. Thanks again. You have helped me out with so much.
User | Count |
---|---|
43 | |
28 | |
23 | |
14 | |
14 |