How do I convert date/time formats differently?

[Ruidil]

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.

 

[Jacob Skaria]

Hi "Ruidil"

If the data is in date/time format you just need to substract it
Say you have FROM date/time in A1 and TO date/time in B1
To substract in C1 enter formula =B1-A1 and custom format that cell to
[h]:mm:ss

OR

Now if your initial data is in text format you need to convert it to time
format
Suppose you have data in Col A, Cell A1
20090606 132400
In B1 enter formula

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

and format B1 to which ever format you would like

Once your data is in date format you can find the difference using above
said method and format the cell to [h]:mm:ss to get the total hour difference.


If this post helps click Yes

 

[Jacob Skaria]

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

 

[Teethless mama]

Try this:

=TEXT(LEFT(A1,8)&TEXT(MID(A1,10,9),"000000"),"0000-00-00 00\:00\:00")+0

 

[Ron Rosenfeld]

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.

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+
TEXT(TRIM(MID(A1,FIND(" ",A1),10)),"00\:00\:00")

--ron