Importing CSV into Excel

Question

I am simply importing CSV into Excel, In which I have a date column. it seem like this.

10.22.2014 13:34:00

When I am finish Importing Now I want to convet the whole date column to look like this in the format cell section but it is not working for me. Can you suggest another way. What can be the main reason SUppose i put a formula on the column then Everytime user import the data he need formula which might be not a good idea, Is there something I can do when I m importing or just like wondering what could be done?

10/22/2014 1:34:00 AM or PM

Answer 1

Here's one approach:

1. Substitute dots with slashes
2. Use DATEVALUE, TIMEVALUE functions on relevant subsections of this substituted string. Subsections are fetched using LEFT and RIGHT string functions.
   1. These return a serial number for dates (days since 1900) and time (a floating point between 0 and 1). When summed, the value can be represented as both date and time, in a format of your choice, as shown below:

Or, showing formulas:

EDIT: adding all-in-one formula. Note that this will give you the serial value (41934.5652777778), which can then be formated using the built in formats for dates / times--just select the one you want. This does not actually render a string:

=DATEVALUE(LEFT(SUBSTITUTE(A1,".","/"),10))+TIMEVALUE(RIGHT(SUBSTITUTE(A1,".","/"),8))

If, however, you do want a string returned, you can use the TEXT function.

=TEXT(DATEVALUE(LEFT(SUBSTITUTE(A1,".","/"),10))+TIMEVALUE(RIGHT(SUBSTITUTE(A1,".","/"),8)),"m/d/yyyy h:mm AM/PM")

(This is done in libreoffice, but the same formulas and arguments exist in MS Excel)