Sorting dates as text using UNIQUE formula

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,602
Office Version
  1. 365
Platform
  1. Windows
Hey all

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

Money.v2.xlsm
ABCD
1Mar-22Dec-22
204-Mar-22Mar-22Jun-22Jun-22
304-Jun-22Jun-22Dec-22Mar-21
404-Dec-22Dec-22Mar-21Mar-22
504-Mar-21Mar-21Sep-22Sep-22
604-Sep-22Sep-22
712-Mar-22Mar-22
814-Mar-22Mar-22
908-Jun-22Jun-22
1006-Dec-22Dec-22
1116-Dec-22Dec-22
1211-Sep-22Sep-22
Sheet3
Cell Formulas
RangeFormula
C1:C5C1=UNIQUE(B2:B12)
D1:D5D1=SORT(UNIQUE(C1#))
B2:B12B2=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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
ABCDE
1
204-Mar-22Mar-22Mar-21Mar-21
304-Jun-22Jun-22Mar-22Mar-22
404-Dec-22Dec-22Jun-22Jun-22
504-Mar-21Mar-21Sep-22Sep-22
604-Sep-22Sep-22Dec-22Dec-22
712-Mar-22
814-Mar-22
908-Jun-22
1006-Dec-22
1116-Dec-22
1211-Sep-22
13
Sort Dates
Cell Formulas
RangeFormula
B2:B6B2=UNIQUE(A2:A12-DAY(A2:A12)+1)
C2:C6C2=SORT(B2#)
E2:E6E2=SORT(UNIQUE(A2:A12-DAY(A2:A12)+1))
Dynamic array formulas.
 
Upvote 0
Solution
.. or if you did want to be dealing with text values instead of actual dates. Again 2 or 1 step

22 03 20.xlsm
ABCDE
1
204-Mar-22Mar-22Mar-21Mar-21
304-Jun-22Jun-22Mar-22Mar-22
404-Dec-22Dec-22Jun-22Jun-22
504-Mar-21Mar-21Sep-22Sep-22
604-Sep-22Sep-22Dec-22Dec-22
712-Mar-22
814-Mar-22
908-Jun-22
1006-Dec-22
1116-Dec-22
1211-Sep-22
Sort Dates (2)
Cell Formulas
RangeFormula
B2:B6B2=UNIQUE(TEXT(A2:A12,"mmm-yy"))
C2:C6C2=SORTBY(B2#,DATEVALUE(1&B2#))
E2:E6E2=UNIQUE(SORTBY(TEXT(A2:A12,"mmm-yy"),A2:A12-DAY(A2:A12)+1))
Dynamic array formulas.
 
Upvote 0
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
ABCDE
1
204-Mar-22Mar-22Mar-21Mar-21
304-Jun-22Jun-22Mar-22Mar-22
404-Dec-22Dec-22Jun-22Jun-22
504-Mar-21Mar-21Sep-22Sep-22
604-Sep-22Sep-22Dec-22Dec-22
712-Mar-22
814-Mar-22
908-Jun-22
1006-Dec-22
1116-Dec-22
1211-Sep-22
13
Sort Dates
Cell Formulas
RangeFormula
B2:B6B2=UNIQUE(A2:A12-DAY(A2:A12)+1)
C2:C6C2=SORT(B2#)
E2:E6E2=SORT(UNIQUE(A2:A12-DAY(A2:A12)+1))
Dynamic array formulas.
Perfect - Thanks @Peter_SSs
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top