Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
format a six figure number to a time.
Hi there
I'm an inexperienced excel user but have been given a task to present some raw data spewed out from a telephone exchange to a note pad file into an excel spreadsheet. From the outset I've had difficulty in transferring the data over but have been following the help files provided by office online and have, up until now, muddled through. My latest problem has had me tearing my hair out for the last four hours or so. Notepad gives me a six digit figure eg 105306 which is the time mark for an event ie 10:53 and 6 secs. How can I use this 6 digit figure to generate a time format within a cell in excel? Every time I try to format the cell it just gives me nonsense output, something like 00:00:00. I'm pretty sure it's a boneheaded question for some of you experts but can anybody give me a clue? Thanks in advance Adrian |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
format a six figure number to a time.
Hi!
What does 105306 represent? 10 hrs 53 mins 06 secs 10:53:06 AM 10:53:06 PM Are all the strings 6 digits? Can you provide more samples? Biff "Ade" wrote in message ... Hi there I'm an inexperienced excel user but have been given a task to present some raw data spewed out from a telephone exchange to a note pad file into an excel spreadsheet. From the outset I've had difficulty in transferring the data over but have been following the help files provided by office online and have, up until now, muddled through. My latest problem has had me tearing my hair out for the last four hours or so. Notepad gives me a six digit figure eg 105306 which is the time mark for an event ie 10:53 and 6 secs. How can I use this 6 digit figure to generate a time format within a cell in excel? Every time I try to format the cell it just gives me nonsense output, something like 00:00:00. I'm pretty sure it's a boneheaded question for some of you experts but can anybody give me a clue? Thanks in advance Adrian |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
format a six figure number to a time.
Hello Ade, You need to use string functions to break the time code apart since this a non-standard Excel format for time. VBA EXAMPLE: Dim H, M, S Dim TimeCode As String TimeCode = "105306" H = Left(TimeCode, 2) & ":" M = Mid(TimeCode,3, 2) & ":" S = Right(TimeCode, 2) Range("A1") = H & M & S A1 will display 10:53:06 There are equivalent Worksheet Functions to do this also. WORKSHEET FORMULA EXAMPLE: A1 = 105306 B1 contains the formula =LEFT(A1, 2) & ":" & MID(A1, 3, 2) & ":" & RIGHT(A1, 2) B1 will display 10:53:06 Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=503554 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
format a six figure number to a time.
The following should get them into Excel time format, assuming the 6
digit number is in cell A1: =VALUE(LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)) Format as time and 13:30:55. Copy the formula down. I assume that all numbers are 6 digit, i.e. that you have leading zeroes for a time like 8:15:00. Hope this helps. Pete |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
format a six figure number to a time.
One way would be to use a formula to create the desired format i.e. 105386 in
cell A1. In B1, LEFT(A1,2)&":"&MID(A1,3,2) would give 10:53 and in C1, RIGHT(A1,2) would give 06 This may not be what you want if you're hoping to do some calculations with the results "Ade" wrote: Hi there I'm an inexperienced excel user but have been given a task to present some raw data spewed out from a telephone exchange to a note pad file into an excel spreadsheet. From the outset I've had difficulty in transferring the data over but have been following the help files provided by office online and have, up until now, muddled through. My latest problem has had me tearing my hair out for the last four hours or so. Notepad gives me a six digit figure eg 105306 which is the time mark for an event ie 10:53 and 6 secs. How can I use this 6 digit figure to generate a time format within a cell in excel? Every time I try to format the cell it just gives me nonsense output, something like 00:00:00. I'm pretty sure it's a boneheaded question for some of you experts but can anybody give me a clue? Thanks in advance Adrian |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
format a six figure number to a time.
On Fri, 20 Jan 2006 23:45:23 -0000, "Ade"
wrote: Hi there I'm an inexperienced excel user but have been given a task to present some raw data spewed out from a telephone exchange to a note pad file into an excel spreadsheet. From the outset I've had difficulty in transferring the data over but have been following the help files provided by office online and have, up until now, muddled through. My latest problem has had me tearing my hair out for the last four hours or so. Notepad gives me a six digit figure eg 105306 which is the time mark for an event ie 10:53 and 6 secs. How can I use this 6 digit figure to generate a time format within a cell in excel? Every time I try to format the cell it just gives me nonsense output, something like 00:00:00. I'm pretty sure it's a boneheaded question for some of you experts but can anybody give me a clue? Thanks in advance Adrian You first need to convert that six digit number into something Excel will understand as a time; and then format that result appropriately: e.g. with data in A1 B1: =--TEXT(A1,"00\:00\:00") Then Format/Cells/Number/Custom Type: hh:mm:ss --ron |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
format a six figure number to a time.
=LEFT(A1,2)&":"&MID(A1,3,2)&" and "&IF(MID(A1,5,1)="0",RIGHT(A1,1)&"
Secs",RIGHT(A1,2)&" secs") Vaya con Dios, Chuck, CABGx3 "Ade" wrote in message ... Hi there I'm an inexperienced excel user but have been given a task to present some raw data spewed out from a telephone exchange to a note pad file into an excel spreadsheet. From the outset I've had difficulty in transferring the data over but have been following the help files provided by office online and have, up until now, muddled through. My latest problem has had me tearing my hair out for the last four hours or so. Notepad gives me a six digit figure eg 105306 which is the time mark for an event ie 10:53 and 6 secs. How can I use this 6 digit figure to generate a time format within a cell in excel? Every time I try to format the cell it just gives me nonsense output, something like 00:00:00. I'm pretty sure it's a boneheaded question for some of you experts but can anybody give me a clue? Thanks in advance Adrian |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
format a six figure number to a time.
Thanks to all of you who replied, it's worked a treat.
All the best Ade "CLR" wrote in message ... =LEFT(A1,2)&":"&MID(A1,3,2)&" and "&IF(MID(A1,5,1)="0",RIGHT(A1,1)&" Secs",RIGHT(A1,2)&" secs") Vaya con Dios, Chuck, CABGx3 "Ade" wrote in message ... Hi there I'm an inexperienced excel user but have been given a task to present some raw data spewed out from a telephone exchange to a note pad file into an excel spreadsheet. From the outset I've had difficulty in transferring the data over but have been following the help files provided by office online and have, up until now, muddled through. My latest problem has had me tearing my hair out for the last four hours or so. Notepad gives me a six digit figure eg 105306 which is the time mark for an event ie 10:53 and 6 secs. How can I use this 6 digit figure to generate a time format within a cell in excel? Every time I try to format the cell it just gives me nonsense output, something like 00:00:00. I'm pretty sure it's a boneheaded question for some of you experts but can anybody give me a clue? Thanks in advance Adrian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel should format time down to the hundredth/sec. | Excel Discussion (Misc queries) | |||
" / " Changing Decimal number format to Fraction on Protected Cell | Excel Worksheet Functions | |||
number format | Excel Discussion (Misc queries) | |||
entering numbers to display a time format | Excel Discussion (Misc queries) | |||
How to format a number in Indian style in Excel? | Excel Discussion (Misc queries) |