Ask Your Question
0

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
1

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 4,349 times

Last updated: Apr 13 '17