Change to day format

[Blue Fish]

Hello:

I have import a text file that the Raw Data is "20070722130054". How
can I make the excel to recognize as "2007-07-22 13:00:54"?

Thanks!

 

[Pete_UK]

Assuming your value is in A2, but this in B2:

=DATE(LEFT(A2,4),MID(A2,5,2),MID(A2,7,2))
+VALUE(MID(A2,9,2)&":"&MID(A2,11,2)&":"&RIGHT(A2,2))

Format the cell as Custom --> yyyy-mm-dd hh:mm:ss, then copy the
formula down.

Hope this helps.

 

[Guest]

In a helper column:

try:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))

Assuming text field is in A1 (etc)

format as CUSTOM: yyyy-mm-dd hh:mm:ss

Then copy/paste special->values in your helper column.

HTH