Entering time using decimal place or Colon

**Mira Sheridan** · 02-07-2012, 11:17 AM

Hi folks,

I did a search on here and found some code that does what i want (mostly) which is to allow users to enter a time in either 7.3 or 7:30 and have it display as 7:30. Except it doesn't work if some one enters a time less than 1 for expamle enters 0.3 (meaning 0:30) it shows 7:12

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C4:V23"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsNumeric(.Value) And .Value >= 1 Then
.Value = (Int(.Value) + (.Value - Int(.Value)) * 100 / 60) / 24
.NumberFormat = "[h]:mm"
End If
End With
End If

ws_exit:
Application.EnableEvents = TrueEnd Sub

I have tried changing the code

If IsNumeric(.Value) And .Value >= 1 Then

to

If IsNumeric(.Value) And .Value >= 0 Then

but then it will not acept time entered with a colon.

Can anyone help me, what do i need to change?

I'm using excel 2003

Thanks, Mira.

**Bob Phillips** · 02-07-2012, 11:27 AM

That look suspiciously like my old code.

Anyway

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C4:V23"
    
    On Error GoTo ws_exit:
    Application.EnableEvents = False

    If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
    
        With Target
        
            If IsNumeric(.Value) And .Value >= 0 Then
            
                .Value = (Int(.Value) + (.Value - Int(.Value)) * 100 / 60) / 24
                .NumberFormat = "[h]:mm"
            End If
        End With
    End If
    
ws_exit:
    Application.EnableEvents = True
End Sub