My Calc spreadsheet contains a column that is categorized as a “Date” field, formatted as yyyy-mm-dd. This works great for the vast majority of the records when I sort by the Date column.
.
However, because I was not always certain about the exact date for some of these records, I also entered the date field with the following formats:
1950s
1953_circa
1944-xx-xx
1968-10-xx
.
PROBLEM: Unfortunately, when I now sort by the “Date” field, all rows with variations appear at the very bottom of the spreadsheet.
.
I would like for all the records to be sorted by the YEAR at least (the first 4 digits of the field). I tried converting the dates to a “Text” category, but got unexpected results.
.
Does anyone know how I may fix this problem? It would be much appreciated.
These data are not dates, but they are strings. The Numeric data and strings in same columns causes such problems.
Use same data type in the whole column.
.
Or use a column for the numeric YEAR data, and more columns for the months, etc…
Thank you!
The most reliable representation of a date is by a string giving it as ISO 8601 extended specifies. Numbers formatted as dates are a crutch depending on a NullDate setting which is all but unambiguous in the IT world.
ISO strings are also the only well specified way to communicate dates. They basically even say “I am a date” and they sort alphabetically correctly.
In the attached example sheet, the ASIFDATE column contains either an actual date in the form of a character string that complies with the ISO 8601 format, or an approximate or paraphrased description that follows this date representation as far as possible. At the same time, you can even calculate with the complete correct ISO dates contained (only if the days described actually exist in the calendar, of course).
See: disask99886SortDatesWithGlitches.ods (38.5 KB)