It is a quiet crisis: a date column that cannot be sorted. When a spreadsheet holds eleven different date formats, the data is not messy, it is untrustworthy. And untrustworthy data is worse than no data at all. The user who inherited this column has tried the obvious fix, highlighting the cells and changing the number format, only to watch half of them refuse to budge. That is the moment when frustration turns into something deeper: a loss of confidence in the tool itself. Excel sees text where the user sees a date, and no amount of formatting will bridge that gap.
This is not a user error. It is a design limitation that has been baked into spreadsheet software for decades. The problem is that dates like "11-08-2016" are ambiguous by nature. Is that November 8 or August 11? The spreadsheet does not know, and it will not guess. Worse, when a cell contains "11 August 2016" as text, the software treats it as a string, not a date value. Changing the number format does nothing because the cell has no number to format. The user is fighting the tool's assumptions about what data looks like, and the tool is winning.
The fastest path to sanity is a two-step process that treats every date as a fresh import. First, use the "Text to Columns" feature (in Excel) or "Split text to columns" (in Google Sheets) with the delimiter set to space, slash, or dash. This breaks each entry into its components, day, month, year, regardless of original format. Then, reassemble those components into a single column using the DATE() function: DATE(year, month, day). For entries that already read as dates, this function preserves the value. For entries stuck as text, it converts them cleanly. The result is a column where every cell contains a real date, and every date follows YYYY-MM-DD.
The real lesson here is not about which formula to type. It is about recognizing that legacy spreadsheet tools were never designed to handle the messy, human-generated data that real teams produce every day. They assume clean input and punish anything else. A date column should not require a rescue operation. It should be a foundation, not a puzzle. The user who posted this question is not asking for a workaround, they are asking for a tool that understands their data the way they do. Until that tool arrives, the fastest fix is to stop fighting the format and start rebuilding it from the ground up.