Just need to edit this to also include 123:58:59

Try this version, below.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myTime As Date
If Target.Cells.Count 1 Then Exit Sub
If Len(Target.Value) < 6 And Len(Target.Value) < 7 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
myTime = TimeValue(CStr(CInt(Left(Target.Value, 2 + _
IIf(Len(Target.Value) = 7, 1, 0))) Mod 24) & ":" & _
Mid(Target.Value, 3 + IIf(Len(Target.Value) = 7, 1, 0), 2) _
& ":" & Right(Target.Value, 2)) + _
IIf(Len(Target.Value) = 7, CInt(Left(Target.Value, 3) / 24), 0)
Target.NumberFormat = "[hh]:mm:ss"
Target.Value = myTime
Application.EnableEvents = True
End Sub


"houghi" wrote in message ...
Bernie Deitrick wrote:
houghi,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

Format the cells where you want to enter the time as TEXT, then enter all the times as 6 digit
number strings (with leading zeroes, if needed)

001123 will result in 00:11:23

120345 will give 12:03:45

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myTime As Date
If Target.Cells.Count 1 Then Exit Sub
If Len(Target.Value) < 6 Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
Application.EnableEvents = False
myTime = TimeValue(Left(Target.Value, 2) & ":" & _
Mid(Target.Value, 3, 2) & ":" & Right(Target.Value, 2))
Target.NumberFormat = "hh:mm:ss"
Target.Value = myTime
Application.EnableEvents = True
End Sub

This is the one, although the autocorrect also are good.
Just need to edit this to also include 123:58:59.

Thanks, this makes work a LOT easier. Luckily there are no other fields
that use 6 digits, otherwise the autoreplace would have been easier

houghi
--
Listen do you hear them drawing near in their search for the sinners?
Feeding on the power of our fear and the evil within us.
Incarnation of Satan's creation of all that we dread.
When the demons arrive those alive would be better off dead!