Mar 17 2021 12:57 PM
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-dd list.
I am using the English UK date style, and have tried creating "custom" formats, as well as different excel formats, but I cannot get this to work.
I need to keep year-month(if)-date(if) display .
Any ideas?
Mar 17 2021 01:08 PM
dates in Excel are actually integer numbers starting from 01 Jan, 1990 as 1. For example 17 Mar 2021 is actually 44272. Applying formats like yyyy-mm-dd, yyyy-mm, or yyyy you only change visual representation of such numbers, it shall not affect sorting.
However, if you have texts as "2021-03-17" and "2021-03" they'll be the text, doesn't matter which format you apply to them, such formatting doesn't affect text value. They are always will be sorted as texts in alphabetical order.
Thus the question is what is your data actually, texts or numbers.
Mar 17 2021 01:13 PM - edited Mar 17 2021 01:14 PM
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.
Mar 17 2021 01:18 PM
Mar 17 2021 01:20 PM
If you have texts, not dates, that's how @Hans Vogelaar suggested