Ask Your Question

how to convert a dd-mm-yyyy string to a date

asked 2017-04-13 21:59:03 +0100

wybodekker gravatar image

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-04-13 22:28:30 +0100

Lupp gravatar image

updated 2017-04-13 22:40:02 +0100

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
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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-04-13 21:59:03 +0100

Seen: 2,195 times

Last updated: Apr 13 '17