I have a column containing string of the form dd-mm-yyyy, representing dates.
How can I convert those to real dates, so that they can be sorted correctly?
Assuming a text-date in the given format is contained in cell A1, and the exact format (number of digits per part!) is as described:
To do the conversion to the numeric representation by a formula you can use
=DATE(VALUE(RIGHT(A1;4));VALUE(MID(A1;4;2));VALUE(LEFT(A1;2)) or
=DATEVALUE(RIGHT(A1;4)&"-"&MID(A1;4;2)&"-"&LEFT(A1;2).
You will need to format the target cell to the preferred format to display dates in addition. This will also work if the date in A1 is a formula result.
To do the conversion in situ you have to use the tool ‘Data’>‘Text to Columns…’ for one column at a time.
In the dialog opening (like the one for importing from csv) click on the column label and choose the ‘Column type’ D/M/Y .
You should also consider where the text-dates came from. If this was import from csv you missed to make the additional setting as described during the import.
Another consideration:
The internationally standardized date format is YYYY-MM-DD as of ISO 8601. I found one single country (NL) where the format you described in your question actually is used. The even more distorted version “D-M-YYYY” is reported to be standard for Tamil speaking Sri Lanka.
Just as a note, the DD-MM-YY format is what’s actually used everywhere but the US and a few other countries.
@nr5: This is only correct concerning the order and (sigh!) length of the main parts. The hyphen-minus as the separator is only used in NL as far as I can see. Other regions preferring the mentioned order (with) the silly two-digit year mostly) either use the point (DD.MM.YY; non-English locales) or the slash (DD/MM/YY; English based locales+) for the purpose.
Regarding the increasing importance of communicating unambiguous dates (also in print or as text) globally, the only reasonable date format is ISO 8601 extended.