Convert 100 to 0100

[rdstagg]

I am trying to convert times.
I am using
=TIME(LEFT(H2,2),RIGHT(H2,2),0)
The only problem I have is the times from 100(1 am) to 900(9am) where I am missing the extra zero at the front.
I know there must be a simple way round this but I can't seem to do it!!

0 = midnight = 00:00
100 = 1am = 01:00
200 = 2am = 02:00 etc

 

[Domenic]

Maybe...

=IF(LEN(A1)=1,TIME(0,0,0),IF(LEN(A1)=3,TIME(LEFT(A1),RIGHT(A1,2),0),TIME(LEFT(A1,2),RIGHT(A1,2),0)))

Hope this helps!

 

[zilpher]

Try applying a format? Format/Cells, you want either hhmm or hh:mm

 

[rdstagg]

Zilpher - tried that - didn't work
domenic - many thanks -superb piece of work and all resolved in under 30 mins - what a marvellous resource this forum is.