If you have spaces after the entry like

"20090606 130201 "

the earlier formula will return error #value; in which case use the below
formula to convert the text cell to date/time format.


All in one line

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+
TIME(LEFT(RIGHT("00" & MID(TRIM(A1),FIND(" ",A1)+1,10),6),2),
MID(RIGHT("00" & MID(TRIM(A1),FIND(" ",A1)+1,10),6),3,2),
RIGHT(RIGHT("00" & MID(TRIM(A1),FIND(" ",A1)+1,10),6),2))

and format it to which ever date/time format as you need.

If this post helps click Yes
---------------
Jacob Skaria


"Ruidil" wrote:

Hi folks. Whenever I pull data from my work dat server, I will get a
date/time data in this yyyymmdd hhmmss format. If the some times I get the
date/time data in yyyymmdd mmss, signifiying that the ommitted hh is 00. how
do I change the date/time format into something simpler, so that I can
calculate the time difference in terms of hh:mm:ss ? Thanks.