Worksheet_Change and Time

I have to input a LARGE number of "times".
Instead of typing in the Colon, I would like simply to type in e.g. "1147", and then the "Worksheet_Change" routine would change that to "11:47", as a time, that I can then work with - in particular, to subtract from another to give the duration.

But I just CANNOT get it to work !

Please Login or Register  to view this content.

The format of the relevant columns is Time as hh:mm

Right before change cell, turn off Events then turn on, to avoid Change-event active again
your original code should be:

PHP Code: 

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Integer, myCol As Integer
Dim Hours, Minutes, myValue, myAnswer
myRow = Target.Row
myCol = Target.Column
myValue = Cells(myRow, myCol)
If myCol = 3 Or myCol = 8 Or myCol = 13 Then
    Hours = Val(Left(Cells(myRow, myCol), 2))
    Minutes = Val(Right(Cells(myRow, myCol), 2))
    myAnswer = TimeSerial(Hours, Minutes, 0)
    Application.EnableEvents = False
        Cells(myRow, myCol) = myAnswer
    Application.EnableEvents = True
End If
End Sub 


And, shorter version

PHP Code: 

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C:C,H:H,M:M")) Is Nothing Then Exit Sub ' do nothing if change in other range
Dim Hours, Minutes, myAnswer
Hours = Val(Left(Target, 2))
Minutes = Val(Right(Target, 2))
myAnswer = TimeSerial(Hours, Minutes, 0)
Application.EnableEvents = False ' turn off change event
    Target.Value = myAnswer
Application.EnableEvents = True ' turn on change event
End Sub 


Thank you, Greg, for your "one liner" but . . .

When I type in 1145, I expect 11:45 as a time, but I get 01:29 ! I'm confused.