Convert number to time

[NCSemon]

Hi all,

I cannot figure out how to convert a number to time format (eg: 1340 to
13:40). I've tried following the directions 'here'
(http://www.exceltip.com/st/Converting_a_Number_to_a_Time_Value/99.html),
but there seems to be an error in the IF statement (no logic check?). I
can't seem to fix it. Any help would be greatly appreciated.

Thanks in advance,
Nick

 

[Guest]

Hi,

use:

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

hth
regards from Brazil
Marcelo

"NCSemon" escreveu:

 

[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

 

[Guest]

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

 

[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.

 

[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!