Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I convert a general number to a time format?
I'm trying to conver 425033 to 42:50:30
I'm running out of steam! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I convert a general number to a time format?
You can parse the string with
=LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2) Dave "doveness" wrote in message ... I'm trying to conver 425033 to 42:50:30 I'm running out of steam! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I convert a general number to a time format?
Dave's formula will return a text result which will look like a time.
If you want the result in true Excel time format (numeric) then you will have to put VALUE( ... ) around his formula and format the cell using a custom format of [hh]:mm:ss. Hope this helps. Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I convert a general number to a time format?
Hi Dave:
Good answer. A slight variation will give a time in standard numerical format: =LEFT(A1,2)/24+MID(A1,3,2)/(24*60)+RIGHT(A1,2)/(24*60*60) format as [hh]:mm:ss -- Gary's Student "Dave Sheldon" wrote: You can parse the string with =LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2) Dave "doveness" wrote in message ... I'm trying to conver 425033 to 42:50:30 I'm running out of steam! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I convert a general number to a time format?
If you have hrs between 1 and 10 you will have only 5 numbers and only the
RIGHT formula will give correct answer. Then you have to modify your formula like this: =IF(LEN(A1)=5;"0"&LEFT(A1;1)&":"&MID(A1;2;2)&":"&R IGHT(A1;2);LEFT(A1;2)&":"&MID(A1;3;2)&":"&RIGHT(A1 ;2)) if below 1 hrs you maybe have only 4. Then you have to modify even further: =IF(LEN(A1)=4;"00"&":"&MID(A1;1;2)&":"&RIGHT(A1;2) ;IF(LEN(A1)=5;"0"&LEFT(A1;1)&":"&MID(A1;2;2)&":"&R IGHT(A1;2);LEFT(A1;2)&":"&MID(A1;3;2)&":"&RIGHT(A1 ;2))) *gublues Gary''s Student skrev: Hi Dave: Good answer. A slight variation will give a time in standard numerical format: =LEFT(A1,2)/24+MID(A1,3,2)/(24*60)+RIGHT(A1,2)/(24*60*60) format as [hh]:mm:ss -- Gary's Student "Dave Sheldon" wrote: You can parse the string with =LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2) Dave "doveness" wrote in message ... I'm trying to conver 425033 to 42:50:30 I'm running out of steam! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I convert a general number to a time format?
Your comments are correct. The formula is designed to handle 6 digit
quantities that can be mapped: hhmmss It will fail for hours less than 10. It will fail for hours greater than 99. The formula will, however, handle numbers as per the OP's spec. -- Gary's Student "gublues" wrote: If you have hrs between 1 and 10 you will have only 5 numbers and only the RIGHT formula will give correct answer. Then you have to modify your formula like this: =IF(LEN(A1)=5;"0"&LEFT(A1;1)&":"&MID(A1;2;2)&":"&R IGHT(A1;2);LEFT(A1;2)&":"&MID(A1;3;2)&":"&RIGHT(A1 ;2)) if below 1 hrs you maybe have only 4. Then you have to modify even further: =IF(LEN(A1)=4;"00"&":"&MID(A1;1;2)&":"&RIGHT(A1;2) ;IF(LEN(A1)=5;"0"&LEFT(A1;1)&":"&MID(A1;2;2)&":"&R IGHT(A1;2);LEFT(A1;2)&":"&MID(A1;3;2)&":"&RIGHT(A1 ;2))) *gublues Gary''s Student skrev: Hi Dave: Good answer. A slight variation will give a time in standard numerical format: =LEFT(A1,2)/24+MID(A1,3,2)/(24*60)+RIGHT(A1,2)/(24*60*60) format as [hh]:mm:ss -- Gary's Student "Dave Sheldon" wrote: You can parse the string with =LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2) Dave "doveness" wrote in message ... I'm trying to conver 425033 to 42:50:30 I'm running out of steam! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do I convert a general number to a time format?
A simpler way..... =TEXT(A1,"00\:00\:00")+0 format as [h]:mm:ss -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=545686 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert data into standard military time format | Excel Discussion (Misc queries) | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions | |||
convert number to text and format it. | Excel Worksheet Functions | |||
how do i convert a number into minutes for a time of completion c. | Excel Discussion (Misc queries) | |||
Help Q: Entering 7 p time format, does not convert to 19:00 | Excel Discussion (Misc queries) |