Convert number to time

M

Marcelo

Hi,

use:

=time(left(a1,2),rigth(a1,2),0)

hth
regards from Brazil
Marcelo

"NCSemon" escreveu:
 
B

bplumhoff

Hi Nick,

There seems to be a typo in your link: A31000 should be A3<1000, I
think.

But I suggest to take:
=TIMEVALUE(LEFT(TEXT(A1,"#000"),LEN(TEXT(A1,"#000"))-2)&":"&RIGHT(TEXT(A1,"#000"),2))

HTH,
Bernd
 
T

Toppers

Nick,
There is an error in the example you looked at (a "<" missing in
the IF statement ...A31000 should be A3<1000).

The following works OK with cell formatted as hh:mm

=IF(A1<1000,TIMEVALUE(LEFT(A1,1)&":"&RIGHT(A1,2)),TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2)))

HTH
 
D

Dave Peterson

Some more:
13 minutes 40 seconds returned from 1340???
=--TEXT(A1,"00\:00\:00")

13 hours 40 minutes 0 seconds???
=--TEXT(A1,"00\:00\:\0\0")

Format either as time.
 
N

NCSemon

Thanks, all!

Dave: Your suggestion (for hh/mm/ss) worked great.

Marcelo and Toppers: Had some problems with yours returning odd values
for some times in the 00 hour range, but Dave's equation took care of
it. I'll provide you examples if you're terribly interested, but I
appreciate your help either way.

Thanks again!
 
Top