Including leading zeros in a time function

Hi all,

I am trying to convert time values from an external source into an Excel friendly format.
I currently have this:
192546
182106
etc
I had an issue with 24 clock times because of leading zeros, eg: 00:00:03. I fixed this by changing the cell format to Custom, 000000.
I am now trying to use a formula: =TIME(LEFT(AD2,2),MID(AD2,3,2),RIGHT(AD2,2)) where AD2 contains the times, however whenever it comes across a time with leading zeros it returns the wrong times. See picture and attached spreadsheet example.
Screen Shot 2015-07-09 at 11.53.35.png
Is there anyway I can alter the formula to include leading zeros in the calculation?
Bonus: Is there anyway of combining all of those formulas (in pic and spreadsheet) into one super formula?

Many thanks in advance!
Chakyt

Wow! Thank you!
Is there anyway to combine the two formulas in that example spreadsheet? The DATE and the TIME functions?
I've tried but it screws up the formatting and gives the result as eg: 42060 0.964907407407407.
The formula I tried was:
=DATE(LEFT(B12,4),MID(B12,5,2),RIGHT(B12,2))&" "&TIME(LEFT(TEXT(C12,"000000"),2),MID(TEXT(C12,"000000"),3,2),RIGHT(TEXT(C12,"000000"),2))

Thank you!

Or

Please Login or Register  to view this content.