Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
User of : in times
I have recieved a huge list on paper with a LOT of times on it in the
format 12:34:56. Unfortunatly it is not possible to recieve the list digitaly. I need to enter these and the thing that disturbes me most is that I have to type the : which is awkward with the <shift<; Is there a way that I can use the - key on the numeric keyboard to display the : in excel? That would speed things up a LOT for me. I have searched googe and was not able to find anything, so I am not sure wether this is possible. houghi -- Listen do you hear them drawing near in their search for the sinners? Feeding on the power of our fear and the evil within us. Incarnation of Satan's creation of all that we dread. When the demons arrive those alive would be better off dead! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
User of : in times
Hi,
One way might be to create a temporary autocorrect using (say) the period to be autocorrected with a : . You could then delete this autocorrect when your list is entered. Mike "houghi" wrote: I have recieved a huge list on paper with a LOT of times on it in the format 12:34:56. Unfortunatly it is not possible to recieve the list digitaly. I need to enter these and the thing that disturbes me most is that I have to type the : which is awkward with the <shift<; Is there a way that I can use the - key on the numeric keyboard to display the : in excel? That would speed things up a LOT for me. I have searched googe and was not able to find anything, so I am not sure wether this is possible. houghi -- Listen do you hear them drawing near in their search for the sinners? Feeding on the power of our fear and the evil within us. Incarnation of Satan's creation of all that we dread. When the demons arrive those alive would be better off dead! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
User of : in times
If you have a BUNCH of them to enter, then you could change your procedure a
bit and use two columns, one for the hours, and one for the minutes and one for the seconds....no need to enter the colons...... If you then wanted that converted to one cell, you could use =TIME(A1,B1,C1),and then format it as you would. Vaya con Dios, Chuck, CABGx3 "houghi" wrote: I have recieved a huge list on paper with a LOT of times on it in the format 12:34:56. Unfortunatly it is not possible to recieve the list digitaly. I need to enter these and the thing that disturbes me most is that I have to type the : which is awkward with the <shift<; Is there a way that I can use the - key on the numeric keyboard to display the : in excel? That would speed things up a LOT for me. I have searched googe and was not able to find anything, so I am not sure wether this is possible. houghi -- Listen do you hear them drawing near in their search for the sinners? Feeding on the power of our fear and the evil within us. Incarnation of Satan's creation of all that we dread. When the demons arrive those alive would be better off dead! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
User of : in times
houghi,
Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. Format the cells where you want to enter the time as TEXT, then enter all the times as 6 digit number strings (with leading zeroes, if needed) 001123 will result in 00:11:23 120345 will give 12:03:45 HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myTime As Date If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) < 6 Then Exit Sub If Not IsNumeric(Target.Value) Then Exit Sub Application.EnableEvents = False myTime = TimeValue(Left(Target.Value, 2) & ":" & _ Mid(Target.Value, 3, 2) & ":" & Right(Target.Value, 2)) Target.NumberFormat = "hh:mm:ss" Target.Value = myTime Application.EnableEvents = True End Sub "houghi" wrote in message ... I have recieved a huge list on paper with a LOT of times on it in the format 12:34:56. Unfortunatly it is not possible to recieve the list digitaly. I need to enter these and the thing that disturbes me most is that I have to type the : which is awkward with the <shift<; Is there a way that I can use the - key on the numeric keyboard to display the : in excel? That would speed things up a LOT for me. I have searched googe and was not able to find anything, so I am not sure wether this is possible. houghi -- Listen do you hear them drawing near in their search for the sinners? Feeding on the power of our fear and the evil within us. Incarnation of Satan's creation of all that we dread. When the demons arrive those alive would be better off dead! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
User of : in times
If you click on Tools | Autocorrect you can have the symbol - replaced
with :, which is what you want (although it might be even quicker if you have . replaced by : and then you can enter the numbers as 12.34.56 and they will change automatically to 12:34:56). If you don't want this to happen forever, then remember to go back and delete that autocorrect entry when you have finished. Hope this helps. Pete On Dec 6, 2:19 pm, houghi wrote: I have recieved a huge list on paper with a LOT of times on it in the format 12:34:56. Unfortunatly it is not possible to recieve the list digitaly. I need to enter these and the thing that disturbes me most is that I have to type the : which is awkward with the <shift<; Is there a way that I can use the - key on the numeric keyboard to display the : in excel? That would speed things up a LOT for me. I have searched googe and was not able to find anything, so I am not sure wether this is possible. houghi -- Listen do you hear them drawing near in their search for the sinners? Feeding on the power of our fear and the evil within us. Incarnation of Satan's creation of all that we dread. When the demons arrive those alive would be better off dead! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
User of : in times
How about using the giant + key on the number keypad?
Then you can do your data entry and when you're done, select that range and do a single: Edit|replace what: + (Plus key) with: : (colon) replace all Using the hypen caused my entries to change to dates (if they could be interpreted as dates). houghi wrote: I have recieved a huge list on paper with a LOT of times on it in the format 12:34:56. Unfortunatly it is not possible to recieve the list digitaly. I need to enter these and the thing that disturbes me most is that I have to type the : which is awkward with the <shift<; Is there a way that I can use the - key on the numeric keyboard to display the : in excel? That would speed things up a LOT for me. I have searched googe and was not able to find anything, so I am not sure wether this is possible. houghi -- Listen do you hear them drawing near in their search for the sinners? Feeding on the power of our fear and the evil within us. Incarnation of Satan's creation of all that we dread. When the demons arrive those alive would be better off dead! -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
User of : in times
Bernie Deitrick wrote:
houghi, Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. Format the cells where you want to enter the time as TEXT, then enter all the times as 6 digit number strings (with leading zeroes, if needed) 001123 will result in 00:11:23 120345 will give 12:03:45 HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myTime As Date If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) < 6 Then Exit Sub If Not IsNumeric(Target.Value) Then Exit Sub Application.EnableEvents = False myTime = TimeValue(Left(Target.Value, 2) & ":" & _ Mid(Target.Value, 3, 2) & ":" & Right(Target.Value, 2)) Target.NumberFormat = "hh:mm:ss" Target.Value = myTime Application.EnableEvents = True End Sub This is the one, although the autocorrect also are good. Just need to edit this to also include 123:58:59. Thanks, this makes work a LOT easier. Luckily there are no other fields that use 6 digits, otherwise the autoreplace would have been easier houghi -- Listen do you hear them drawing near in their search for the sinners? Feeding on the power of our fear and the evil within us. Incarnation of Satan's creation of all that we dread. When the demons arrive those alive would be better off dead! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
User of : in times
Just need to edit this to also include 123:58:59
Try this version, below. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myTime As Date If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) < 6 And Len(Target.Value) < 7 Then Exit Sub If Not IsNumeric(Target.Value) Then Exit Sub Application.EnableEvents = False myTime = TimeValue(CStr(CInt(Left(Target.Value, 2 + _ IIf(Len(Target.Value) = 7, 1, 0))) Mod 24) & ":" & _ Mid(Target.Value, 3 + IIf(Len(Target.Value) = 7, 1, 0), 2) _ & ":" & Right(Target.Value, 2)) + _ IIf(Len(Target.Value) = 7, CInt(Left(Target.Value, 3) / 24), 0) Target.NumberFormat = "[hh]:mm:ss" Target.Value = myTime Application.EnableEvents = True End Sub "houghi" wrote in message ... Bernie Deitrick wrote: houghi, Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the window that appears. Format the cells where you want to enter the time as TEXT, then enter all the times as 6 digit number strings (with leading zeroes, if needed) 001123 will result in 00:11:23 120345 will give 12:03:45 HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) Dim myTime As Date If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) < 6 Then Exit Sub If Not IsNumeric(Target.Value) Then Exit Sub Application.EnableEvents = False myTime = TimeValue(Left(Target.Value, 2) & ":" & _ Mid(Target.Value, 3, 2) & ":" & Right(Target.Value, 2)) Target.NumberFormat = "hh:mm:ss" Target.Value = myTime Application.EnableEvents = True End Sub This is the one, although the autocorrect also are good. Just need to edit this to also include 123:58:59. Thanks, this makes work a LOT easier. Luckily there are no other fields that use 6 digits, otherwise the autoreplace would have been easier houghi -- Listen do you hear them drawing near in their search for the sinners? Feeding on the power of our fear and the evil within us. Incarnation of Satan's creation of all that we dread. When the demons arrive those alive would be better off dead! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging times (similar to lap times) | Excel Discussion (Misc queries) | |||
Calculation of hourly rate times hours times 1.5 | Excel Worksheet Functions | |||
How are relay leg times or driving times entered and totaled? | Excel Worksheet Functions | |||
=Text(b2-A2,"h") hours between 2 times, what do I do for 10 times | New Users to Excel | |||
Charting and analyzing Times' times data for trends | Excel Discussion (Misc queries) |