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/Convertin...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


--
NCSemon
------------------------------------------------------------------------
NCSemon's Profile: http://www.excelforum.com/member.php...o&userid=35999
View this thread: http://www.excelforum.com/showthread...hreadid=557869

Hi,

use:

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

hth
regards from Brazil
Marcelo

"NCSemon" escreveu:


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/Convertin...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


--
NCSemon
------------------------------------------------------------------------
NCSemon's Profile: http://www.excelforum.com/member.php...o&userid=35999
View this thread: http://www.excelforum.com/showthread...hreadid=557869

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

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

"NCSemon" wrote:


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/Convertin...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


--
NCSemon
------------------------------------------------------------------------
NCSemon's Profile: http://www.excelforum.com/member.php...o&userid=35999
View this thread: http://www.excelforum.com/showthread...hreadid=557869

Some mo
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 wrote:

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/Convertin...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

--
NCSemon
------------------------------------------------------------------------
NCSemon's Profile: http://www.excelforum.com/member.php...o&userid=35999
View this thread: http://www.excelforum.com/showthread...hreadid=557869

--

Dave Peterson

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!


--
NCSemon
------------------------------------------------------------------------
NCSemon's Profile: http://www.excelforum.com/member.php...o&userid=35999
View this thread: http://www.excelforum.com/showthread...hreadid=557869