automatic formatting

[cocacola]

hi there

thanks for all your help...

i'm entering a lot of data that looks like:

0:00
0:04
6:20
13:14, etc.

these are number of hours and minutes. is there a formatting shortcut i can use so i don't have to enter the colon and the preceeding zeros for each entry?

in other words, if i enter "1" EXCEL will enter "0:01". if i enter "647", EXCEL will enter "6:47", "100" becomes "1:00", etc.

thanks muchly!

 

[Andrew Poulsom]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'   Ignore multiple selection
    If Target.Count > 1 Then Exit Sub
'   Ignore text entries
    If Not IsNumeric(Target.Value) Then Exit Sub
'   Restrict to column A
'   *** Change column number to suit ***
    If Target.Column <> 1 Then Exit Sub
    Application.EnableEvents = False
    Select Case Len(Target)
        Case 1: Target.Value = "0:0" & Target
        Case 2: Target.Value = "0:" & Target
        Case Else: Target.Value = Left(Target, Len(Target) - 2) & ":" & Right(Target, 2)
    End Select
    Target.NumberFormat = "hh:mm"
    Application.EnableEvents = True
End Sub

 

[Mark W.]

Not by formatting... Excel needs the colon to recognize the value as one that must be converted into an internal time value, and needing a proper time format; however, you could use a formula (as shown below) to translate an integer value into a corresponding time value.

Book1
	A	B	C	D	E	F	G	H	I	J
1	647	6:47AM
2	1	12:01AM
3	100	1:00AM
4
5
Sheet1

This message was edited by Mark W. on 2002-10-31 10:05