Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
....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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
....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. |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert an entire spread sheet form inches to mills. | Excel Discussion (Misc queries) | |||
CONVERT Function Disappered in Excel | Excel Discussion (Misc queries) | |||
convert decimal number to time : convert 1,59 (minutes, dec) to m | Excel Discussion (Misc queries) | |||
Convert Numeric into Text | Excel Worksheet Functions | |||
Convert Time...!convert tenths of a second | Excel Discussion (Misc queries) |