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-...
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/)
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
- Thanks, will give it a try!