I have checked the format as “date” for the column, but 1-1-2018 is followed by 12-1-2018 instead of 2-1-2018 when I sort. It is sorting as just numbers, not as dates. Am I going to have to redo all the dates??? Isn’t that what formatting it is supposed to handle?
Dates will only sort properly if they are dates. Your data seems to be the text. Entering something into a cell will be checked for what data type it looks like (is that number? date? percent?), and the data will be stored with the detected data type. The date is the most difficult here (because there are so many different date formats out there); and there is a dedicated setting determining which input formats are accepted as dates: Options→Language Settings→Languages→Date acceptance patterns, and they are unrelated to cell format options. Of course, using the ISO date format (2018-01-28) would be detected.
After the data is entered and its type is detected, changing cell formatting will never change the data type. Formatting is the way how the data is shown, not how it’s treated. So even if you mark your cells as date, if they have contained text, they will continue to have text, and no date sorting will be applicable.
To change type of existing data, you need not to change formatting, bit to convert - that is done using Data→Text to Columns, and select the proper data type for the columns by right-clicking the columns headers in the dialog (you’d need Date (DMY)).
1 Like
Thank you very much. This worked for me.
After making the Text to Column change you described, I then changed the Format for the cells I wanted to convert to date and it sorted perfectly after that. Thanks!
Thank you for posting this explanation on how to fix the date sorting problem.
(Hoping to not spread confusion I dare to state:)
Dates represented as texts (strings) perfectly sort lexicographically as the semantics requires if they are given in the one and only reasonable way which is standardized as ISO 8601 extended. The ways dates are represented by numbers in spreadsheets are not ruled by a clear and mandatory standard, but depending on a so-called NullDate setting for which still different (bad) standards exist. The numeric representation therefore is less reliably unambiguous. The import of dates into spreadsheets as “special numbers” always depends on the assurance that the textual representation in the source is of the chosen kind and belongs to the preset century if the evil 2-digit-year representation was used. It always should be followed by setting the respective ranges to ISO 8601 (YYYY-MM-DD), and may be followed by converting them to texts again using that correct exchange format. This way they can be exported (e.g.) to csv style files again without any ambiguity.
Spreadsheets can even calculate based on dates in textual representation by their automatic conversion (reliably if ISO 8601 is used).
1 Like