Convert to Time

[Border Terrier]

You have all been so helpful in the past, so here goes again.

In column A, starting in A4 i have times imported as follows,

155656
102630
092536

Now using the formula =TIME(LEFT(A4,2),MID(A4,3,2),RIGHT(A4,2)) in B4 and pasted down, the result in B4 is 15:56:56 in B5 is 10:26:30, this is fine.

Now the problem is in B6, the zero gets ignored and returns 20:53:36 instead of 09:25:35.

Any help to overcome this is appreciated.
Simon

 

[patrickmuldoon99]

I have simulated your problem on my computer, and it worked fine if the value 092536 is formatted as text

HTH

 

[Scott Huish]

I can't duplicate your problem either, but this formula should also work, format result cells as time.

=TEXT(A4+0,"00\:00\:00")+0

Book1
	A	B	C	D
4	155656	15:56:56
5	102630	10:26:30
6	092536	9:25:36
Sheet1

 

[Border Terrier]

Thanks both of you for your speedy replies.

Indeed patrickmuldoon99, i should of had them formatted as text, thanks for the help.

And HOTPEPPER, your solution works fine leaving them formatted as General, thank you.

Thank again,
Simon