Excel: convert text string to date/time format

Question

I have a column with date/time info in following format stored as text: 03.01.2011 09:55. How can I convert it to Date/Time format?

Answer 1

If A1 is the cell with the information in it then the following formula should work.

=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))+RIGHT(A1,5)

This makes the assumption that 03.01.2011 is January 3rd. If it's March 1st, you need to swap the second and third terms in the DATE function.

Gansi's suggestion will not work for two reasons (one and a half, depending on your location regional settings).

1. Because changing a cell's format from text to a number does not automatically recalculate / alter the data in the cell - you'd need to re-enter the data (e.g. by pressing F2 and then enter)

2. (or 1 1/2)) In UK / US date formatting, Excel would not recognize the dots as delimiters in the date. This may not be true in other regions (possibly Germany?) where this usage is more common.