because this ist what you get as text from an Ebay transaction report as a HTML-file. It can be imported into Calc, but unfortunately one cannot sort by date, since this is a text string.
Welcome @omniscient!
If the string is in the cell N16, then:
=DATEVALUE(N16)+TIMEVALUE(N16)
will return the date/time value.
This does not work. It generates #Name? error.
because your locale is germany? so use »DATUMWERT« and »ZEITWERT« instead, but anyway see my answer…
Import the data properly instead of repairing bad data. In the HTML import dialog, check option “detect special numbers” and choose “English (USA)” as import locale.
2 Likes
Or such a trick. 
Select the problem cells and run the (document) macro:
Option VbaSupport 1
Sub SelToValue
Selection.Value=Selection.Value
End Sub
With or without VBA support, this trick works with ISO strings or US English strings only.
Sub SelToValue
sel = thiscomponent.currentselection
sel.setFormulaArray(sel.getDataArray())
End Sub
1 Like
Yes, good trick. A few clarifications for readers.
The setFormula method expects data in the en-US locale (just like the Range.Formula property in Excel). If the text can be interpreted as a number, date, or boolean value in the en-US locale, then it is cast to the appropriate type.
Why does John’s example use
sel.setFormulaArray(sel.getDataArray())
?
The getFotmula method, in the case where the cell has a text value and can be interpreted as a number, date, or logical value, adds an apostrophe to the left (so that the value does not change during reverse conversion). The method getDataArray returns texts without transformations.
It remains to add that the trick with Selection.Value also works in the case when the selection consists of several rectangular ranges.
Select the Column with wrong imported Dates and use
→Data→Text in Columns →→ without any delimiter set and choose in the preview-Header Date MDY instead Standard
1 Like