Time quirk

[normpam]

The code below works just fine. If someone enters '915' they will get 9:15am, or '1345' and get 1:30pm. But it seems two times never work.... 12:00pm which results in 0:.5 and
1648 which results in 0:.7

Any ideas?

Sub Auto_Open()
    Call SetUp_OnEntry
End Sub

Sub SetUp_OnEntry()
    Application.OnEntry = "SetColon"
End Sub


Sub SetColon()
    If ActiveCell.Column = 3 Or ActiveCell.Column = 2 Or ActiveCell.Column = 4 Or ActiveCell.Column = 5 Then
    If Len(ActiveCell.Value) = 4 Then ActiveCell.Value = Left(ActiveCell.Value, 2) & ":" & Right(ActiveCell.Value, 2)
    If Len(ActiveCell.Value) = 3 Then ActiveCell.Value = Left(ActiveCell.Value, 1) & ":" & Right(ActiveCell.Value, 2)
    End If
End Sub

 

[normpam]

​

As already explained 'cause of the Len trap like you must read within its VBA help and it could happen with some other times …​

Thanks. I did a few more tests and fixed the code. It only happened on times of the day that have a decimal equivalent of just one digit, such as .7 or .8