A bit LOUDER since I am hard of hearing. Please do NOT shout (all caps -
considered rude netiquette)
right click sheet tab>view code>insert ALL of this. Change range to suit.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) 'Ron Rosenfeld
<
[email protected]>
Dim InputRange As Range
Dim Temp As Variant
Dim i As Integer
Dim Separators(2) As String
Separators(0) = ";"
Separators(1) = ","
Separators(2) = "."
Application.EnableEvents = False
Set InputRange = [A1:A100] 'or whatever
If Intersect(Target, InputRange) Is Nothing Then GoTo Bye
If Target.Value = 0 Then GoTo Bye
Temp = Target.Value
'If entry is an integer, then make it a time
On Error GoTo ConvertString
If Int(Temp) = Temp Then
Temp = (Int(Temp / 100) + (Temp / 100 - Int(Temp / 100)) * 100 / 60) /
24
GoTo Last
ElseIf Temp < 1 Then 'probably a time
GoTo Last
End If
If Temp > 15000 Then 'probably a date/time string
Temp = CDate(Temp - Int(Temp))
GoTo Last
End If
ConvertString: 'replace separator with colon unless the string is a
date
On Error GoTo 0
For i = 0 To 2
Temp = Replace(Temp, Separators(i), ":")
Next i
Last:
Target.Value = Temp
Target.NumberFormat = "hh:mm"
Bye: Application.EnableEvents = True
End Sub