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-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?
6 Replies
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?
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.
- thank. But how do I accomplish my goal of being able to sort the dates in order regardless of if they are Year, month, day or year, month or just year?
I understand that there will have to be some type of rule about where does it appear (for example, yyyy in a list of yyyy-mm-dd; beginning or end/)If you have texts, not dates, that's how HansVogelaar suggested
