Forum Discussion
Excel date sorting issue
I have a mix date data in the same column; yyyy-mm-dd, and yyyy-mm, and yyyy . I need to sort it for my research. yyyy-mm-dd date formats sort, BUT the yyyy-mm, and yyyy do not sort within yyyy-mm-...
Are the yyyy-mm-dd and yyyy-mm values text? If so, try the following.
Let's say the values are in A2 and down.
In another column, enter the following formula in row 2:
=IF(LEN(A2)=10,DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)),IF(LEN(A2)=7,DATE(LEFT(A2,4),MID(A2,6,2),1),DATE(A2,1,1)))
Fill down.
Sort the entire range on the new column. You can hide the new column if you prefer.
Would making them text enable me to sort?