Convert "800" to "8:00" or "08:00"
 

Is there a function that will let me convert a values such as 800 to a hh:mm
format?

Thanks.

Try...

=TIME(LEFT(TEXT(A1,"0000"),2),RIGHT(TEXT(A1,"0000" ),2),0)

or

=SUM(MID(TEXT(A1,"0000"),{1,3},2)/{24,1440})

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Format cell as
'Time'.

Hope this helps!

In article ,
"CPK" wrote:

Is there a function that will let me convert a values such as 800 to a hh:mm
format?

Thanks.

....this seems to be a slightly shorter way:

=TIMEVALUE(TEXT(A2,"00\:00"))

or

=TIMEVALUE(TEXT(A1,"00"":""00"))

Regards,
KL


"Domenic" wrote in message
...
Try...

=TIME(LEFT(TEXT(A1,"0000"),2),RIGHT(TEXT(A1,"0000" ),2),0)

or

=SUM(MID(TEXT(A1,"0000"),{1,3},2)/{24,1440})

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Format cell as
'Time'.

Hope this helps!

In article ,
"CPK" wrote:

Is there a function that will let me convert a values such as 800 to a
hh:mm
format?

Thanks.

Try:

=INT(A1/100)/24+RIGHT(A1,2)/1440

Format as time.

Tim C


"CPK" wrote:

Is there a function that will let me convert a values such as 800 to a
hh:mm format?

Thanks.

....even shorter:

=VALUE(TEXT(A1,"00\:00"))
=VALUE(TEXT(A1,"00"":""00"))

KL


"KL" wrote in message
...
...this seems to be a slightly shorter way:

=TIMEVALUE(TEXT(A2,"00\:00"))

or

=TIMEVALUE(TEXT(A1,"00"":""00"))

Regards,
KL


"Domenic" wrote in message
...
Try...

=TIME(LEFT(TEXT(A1,"0000"),2),RIGHT(TEXT(A1,"0000" ),2),0)

or

=SUM(MID(TEXT(A1,"0000"),{1,3},2)/{24,1440})

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Format cell as
'Time'.

Hope this helps!

In article ,
"CPK" wrote:

Is there a function that will let me convert a values such as 800 to a
hh:mm
format?

Thanks.

This formula will return the time value of a three digit or four digit
number in military time format without having to enter ( : ) between
the numbers.

Will also accommodate for entering time by pressing, ( Ctrl + Shift + ;
).

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


--
Flintstone
------------------------------------------------------------------------
Flintstone's Profile: http://www.excelforum.com/member.php...o&userid=15310
View this thread: http://www.excelforum.com/showthread...hreadid=385602