Sorting dates as text using UNIQUE formula

[redspanna]

Hey all

I have some simple data held in sheet1 (ColumnA) which is a list of dates

Money.v2.xlsm
	A	B	C	D
1			Mar-22	Dec-22
2	04-Mar-22	Mar-22	Jun-22	Jun-22
3	04-Jun-22	Jun-22	Dec-22	Mar-21
4	04-Dec-22	Dec-22	Mar-21	Mar-22
5	04-Mar-21	Mar-21	Sep-22	Sep-22
6	04-Sep-22	Sep-22
7	12-Mar-22	Mar-22
8	14-Mar-22	Mar-22
9	08-Jun-22	Jun-22
10	06-Dec-22	Dec-22
11	16-Dec-22	Dec-22
12	11-Sep-22	Sep-22
Sheet3

Cell Formulas
Range	Formula
C1:C5	C1	=UNIQUE(B2:B12)
D1:D5	D1	=SORT(UNIQUE(C1#))
B2:B12	B2	=TEXT(A2,"MMM-YY")
Dynamic array formulas.

I simply want a UNIQUE list of MONTHS-YEARS for these dates and be able to sort them in date order. I have tried converting the given date into MMMM-YY date format (COLUMN B) but then the SORT(UNIQUE) sees them as a text value and sorts alphabetically and not in date order COLUMN D)...

Any ideas how to extract a MONTH-YY from a date then generate a UNIQUE list which is in date order?

Thanks in advance

 

[Peter_SSs]

Not sure if you wanted the two-step process (columns B & C), or a single step (column E)
Columns B, C & E formatted as "mmm-yy"

22 03 20.xlsm
	A	B	C	D	E
1
2	04-Mar-22	Mar-22	Mar-21		Mar-21
3	04-Jun-22	Jun-22	Mar-22		Mar-22
4	04-Dec-22	Dec-22	Jun-22		Jun-22
5	04-Mar-21	Mar-21	Sep-22		Sep-22
6	04-Sep-22	Sep-22	Dec-22		Dec-22
7	12-Mar-22
8	14-Mar-22
9	08-Jun-22
10	06-Dec-22
11	16-Dec-22
12	11-Sep-22
13
Sort Dates

Cell Formulas
Range	Formula
B2:B6	B2	=UNIQUE(A2:A12-DAY(A2:A12)+1)
C2:C6	C2	=SORT(B2#)
E2:E6	E2	=SORT(UNIQUE(A2:A12-DAY(A2:A12)+1))
Dynamic array formulas.

 

[Peter_SSs]

.. or if you did want to be dealing with text values instead of actual dates. Again 2 or 1 step

22 03 20.xlsm
	A	B	C	D	E
1
2	04-Mar-22	Mar-22	Mar-21		Mar-21
3	04-Jun-22	Jun-22	Mar-22		Mar-22
4	04-Dec-22	Dec-22	Jun-22		Jun-22
5	04-Mar-21	Mar-21	Sep-22		Sep-22
6	04-Sep-22	Sep-22	Dec-22		Dec-22
7	12-Mar-22
8	14-Mar-22
9	08-Jun-22
10	06-Dec-22
11	16-Dec-22
12	11-Sep-22
Sort Dates (2)

Cell Formulas
Range	Formula
B2:B6	B2	=UNIQUE(TEXT(A2:A12,"mmm-yy"))
C2:C6	C2	=SORTBY(B2#,DATEVALUE(1&B2#))
E2:E6	E2	=UNIQUE(SORTBY(TEXT(A2:A12,"mmm-yy"),A2:A12-DAY(A2:A12)+1))
Dynamic array formulas.

 

[redspanna]

Not sure if you wanted the two-step process (columns B & C), or a single step (column E)
Columns B, C & E formatted as "mmm-yy"

22 03 20.xlsm
	A	B	C	D	E
1
2	04-Mar-22	Mar-22	Mar-21		Mar-21
3	04-Jun-22	Jun-22	Mar-22		Mar-22
4	04-Dec-22	Dec-22	Jun-22		Jun-22
5	04-Mar-21	Mar-21	Sep-22		Sep-22
6	04-Sep-22	Sep-22	Dec-22		Dec-22
7	12-Mar-22
8	14-Mar-22
9	08-Jun-22
10	06-Dec-22
11	16-Dec-22
12	11-Sep-22
13
Sort Dates

Cell Formulas
Range	Formula
B2:B6	B2	=UNIQUE(A2:A12-DAY(A2:A12)+1)
C2:C6	C2	=SORT(B2#)
E2:E6	E2	=SORT(UNIQUE(A2:A12-DAY(A2:A12)+1))
Dynamic array formulas.

Perfect - Thanks @Peter_SSs

 

[Peter_SSs]

You're welcome.