User of : in times

  • Thread starter Thread starter houghi
  • Start date Start date
H

houghi

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
 
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
 
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,

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
 
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
 
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).
 
Bernie said:
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
Click to expand...

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
 
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
 
Bernie said:
Try this version, below.

HTH,
Click to expand...

It does, thanks.

houghi
--for I in `find /usr/src/linux/ -name *.c`; \
do A=`grep -i -A 1 -B 1 **** $I`;if [ "$A" != "" ]; \
then printf "$I \n$A \n\n"; fi ;done|less
 
Back
Top