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!

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!

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!

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!

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!

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

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!

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!