Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
excellent, many thanks! it is exactly what i needed and could not figure it out myself. thanks again! –  Peter Feb 13 '11 at 19:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.