Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting time from imported data
Hello everyday I import data that looks like this
ACD Time 8:52:40 :20:26 3:46:45 :00:00 1:17:52 3:27:37 Here is the problem if the cell is over an hour , example 8:52:40 the cell is formatted as time, but if the cell is less than an hour example, :20:20 is formatted as general. However if I add a zero in front of the number (e.g. 0:20:26)then it becomes formatted as time. I've have tried several formats to change this but it does not work. The only way is if I change it manually .. On a daily basis I have over 50 that I have to add the zero to . How can I speed this up. thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting time from imported data
The best way would be to change the export program to add zero.
Otherwise, insert a new column next to the date column (I will assume the first date is in A1) Use =IF(LEFT(A1)=":",TIME(0,MID(A1,2,2),MID(A1,5,2)),A 1) in the empty cell next to the 'error' column Format as time and copy down the column by double clicking the fill handle (solid square in lower right of active cell) With all these selected use Copy followed by Paste Special | Values Now you can delete the 'error' column You could experiment with recording a macro to do this. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Wanna Learn" wrote in message ... Hello everyday I import data that looks like this ACD Time 8:52:40 :20:26 3:46:45 :00:00 1:17:52 3:27:37 Here is the problem if the cell is over an hour , example 8:52:40 the cell is formatted as time, but if the cell is less than an hour example, :20:20 is formatted as general. However if I add a zero in front of the number (e.g. 0:20:26)then it becomes formatted as time. I've have tried several formats to change this but it does not work. The only way is if I change it manually . On a daily basis I have over 50 that I have to add the zero to . How can I speed this up. thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting time from imported data
Wanna,
another solution is to create this macro in a module. Public Sub FormatTime() Dim rng As range Dim cell As range Set rng = Selection For Each cell In rng If Left(cell.Value, 1) = ":" Then cell.Value = "0" & cell.Value End If Next End Sub To make things easier, assign this macro to a shortcut, such as ctrl+g. Thus, to use daily just highlight your range and press ctrl+g. "Wanna Learn" wrote: Hello everyday I import data that looks like this ACD Time 8:52:40 :20:26 3:46:45 :00:00 1:17:52 3:27:37 Here is the problem if the cell is over an hour , example 8:52:40 the cell is formatted as time, but if the cell is less than an hour example, :20:20 is formatted as general. However if I add a zero in front of the number (e.g. 0:20:26)then it becomes formatted as time. I've have tried several formats to change this but it does not work. The only way is if I change it manually . On a daily basis I have over 50 that I have to add the zero to . How can I speed this up. thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formatting time from imported data
Thank you Socratis - SUPER
"Socratis" wrote: Wanna, another solution is to create this macro in a module. Public Sub FormatTime() Dim rng As range Dim cell As range Set rng = Selection For Each cell In rng If Left(cell.Value, 1) = ":" Then cell.Value = "0" & cell.Value End If Next End Sub To make things easier, assign this macro to a shortcut, such as ctrl+g. Thus, to use daily just highlight your range and press ctrl+g. "Wanna Learn" wrote: Hello everyday I import data that looks like this ACD Time 8:52:40 :20:26 3:46:45 :00:00 1:17:52 3:27:37 Here is the problem if the cell is over an hour , example 8:52:40 the cell is formatted as time, but if the cell is less than an hour example, :20:20 is formatted as general. However if I add a zero in front of the number (e.g. 0:20:26)then it becomes formatted as time. I've have tried several formats to change this but it does not work. The only way is if I change it manually . On a daily basis I have over 50 that I have to add the zero to . How can I speed this up. thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting on imported data (Excel 2003) | Excel Discussion (Misc queries) | |||
Formatting imported data | Excel Worksheet Functions | |||
Formatting web imported data correctly | Excel Worksheet Functions | |||
convert time imported as text to time format for calculations | Excel Worksheet Functions | |||
problem working with time data imported from text file | Excel Worksheet Functions |