Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range, t, m$, s$
Set r = Intersect(Target, Columns("A"))
If Not r Is Nothing Then
For Each c In r.Cells
If TypeName(c.Value) = "String" Then
t = Split(c.Value, ":")
Select Case UBound(t)
Case 1
m = Format(Val(t(1)) Mod 60, "00")
If t(0) = Val(t(0)) And Right("00" & t(1), 2) = m Then
c.Value = Val(t(0)) / 24 + Val(t(1)) / 1440
End If
Case 2
m = Format(Val(t(1)) Mod 60, "00"): s = Format(Val(t(2)) Mod 60, "00")
If t(0) = Val(t(0)) And Right("00" & t(1), 2) = m And Right("00" & t(2), 2) = s Then
c.Value = Val(t(0)) / 24 + Val(t(1)) / 1440 + Val(t(2)) / 86400
End If
End Select
End If
Next c
End If
End Sub