Entering time values

  • Thread starter Thread starter John Oliver
  • Start date Start date
J

John Oliver

I had asked for a way to go to a cell, enter a value like "1234" and
have that displayed as "00:12:34". I was given the following snippet of
code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("E4:E27", "F4:F27")) Is Nothing Then Exit
Sub

On Error GoTo errHandler:

With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
Select Case .Value
Case 0
Case 1 To 99
.Value = TimeSerial(0, 0, .Value)
.NumberFormat = "hh:mm:ss"
Case 100 To 2359
.Value = TimeSerial(0, Int(.Value / 100), .Value Mod
100)
.NumberFormat = "hh:mm:ss"
Case 10000 To 235959
.Value = TimeSerial(Int(.Value / 10000), _
Int((.Value Mod 10000) / 100), .Value Mod 100)
.NumberFormat = "hh:mm:ss"
Case Else
End Select
End If
End With
errHandler:
Application.EnableEvents = True

End Sub


This mostly works, except it seems that if I enter a 4 digit number that
starts with a 3, 4, or 5, I get a weird date.

For example:

3124 gets me 7/20/1908 12:00:00 AM
4567 gets me 7/2/1912 12:00:00 AM
 
John

The problem is that the cell is formatted for time so Excel thinks you're
entering a date. When you enter a date of 3421, you get the date that is
3,421 days from Jan 1, 1900 12:00AM. Under your Case Else statement in your
code, you should put this line

..NumberFormat = "general"

That will show you what you entered when what you enter is not in the format
that's accepted by your code.
 
John,

There is an error in the code that only allows for 24 minutes, not 60.

Change the line
Case 100 To 2359
to
Case 100 To 5959
 
Back
Top