Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formating time
Hi,
I have a spreadsheet that imports time from another program and looks somewhat like this: Other Time ACW Time 4:35:20 1:23:07 :07:11 :28:03 :24:10 :00:00 :09:49 :07:19 :27:01 :06:19 :19:11 :00:00 :05:44 :12:35 :25:02 :00:00 :14:28 :09:16 1:08:00 :19:35 :12:13 :00:00 :17:54 :00:00 :07:53 :00:00 :36:44 :00:00 The problem I ran into is with the times that are less than an hour; they all show up with a colon in front and not digit. This is causing a problem when I try to add up the times - I get an error. I tried to format the cells as time (13:30:55) but nothing happened. Help! :( TIA, Marta |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formating time
I think you'll find that those times which start with a colon have
actually been imported as text values. You can get round this by using this formula in a helper column (eg in C2): =IF(LEFT(A2,1)=":","0"&A2,A2)*1 Format the cell as a time, copy C2 into D2 to act on B2, and then copy these two down as far as required. You can fix the values and then copy back over the originals, and then delete columns C and D. Hope this helps. Pete On Jul 28, 6:58*pm, Marta wrote: Hi, I have a spreadsheet that imports time from another program and looks somewhat like this: Other Time * * *ACW Time 4:35:20 1:23:07 :07:11 *:28:03 :24:10 *:00:00 :09:49 *:07:19 :27:01 *:06:19 :19:11 *:00:00 :05:44 *:12:35 :25:02 *:00:00 :14:28 *:09:16 1:08:00 :19:35 :12:13 *:00:00 :17:54 *:00:00 :07:53 *:00:00 :36:44 *:00:00 The problem I ran into is with the times that are less than an hour; they all show up with a colon in front and not digit. *This is causing a problem when I try to add up the times - I get an error. *I tried to format the cells as time (13:30:55) but nothing happened. Help! :( TIA, Marta |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formating time
Hi,
Assume your time is in A1 then in a blank cel enter =--(0&A1) And copy and format as necessary -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Marta" wrote: Hi, I have a spreadsheet that imports time from another program and looks somewhat like this: Other Time ACW Time 4:35:20 1:23:07 :07:11 :28:03 :24:10 :00:00 :09:49 :07:19 :27:01 :06:19 :19:11 :00:00 :05:44 :12:35 :25:02 :00:00 :14:28 :09:16 1:08:00 :19:35 :12:13 :00:00 :17:54 :00:00 :07:53 :00:00 :36:44 :00:00 The problem I ran into is with the times that are less than an hour; they all show up with a colon in front and not digit. This is causing a problem when I try to add up the times - I get an error. I tried to format the cells as time (13:30:55) but nothing happened. Help! :( TIA, Marta |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formating Cells for Time | Excel Worksheet Functions | |||
TIME FORMATING | Excel Discussion (Misc queries) | |||
Help with Conditional Formating Time | Excel Worksheet Functions | |||
Time Formating | Excel Worksheet Functions | |||
change time formating | Excel Discussion (Misc queries) |