Enter time in m:ss.00 format with no colons

[rossbritton26]

Hi,

I have a sheet where i enter lots of times in the format m:ss.00. I would like to be able to enter these times without the punctuation, i.e. mss00 but still display in format m:ss.00. I'm guessing there is a vba way of doing this (fingers crossed).

Any help would be appreciated
Thanks,
Ross

 

[VoG]

This may help http://www.cpearson.com/excel/DateTimeEntry.htm

 

[rossbritton26]

Thanks,
That kind of helps, however, i'm still unsure of how to get it to work with minutes, seconds and milliseconds rather than hours and minutes??

Ross.

 

[kpark91]

Hi, rossbritton26.
Try this code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.count > 1 Then Exit Sub
    
    If Len(Target.Value) = 5 And IsNumeric(Target.Value) Then
        Target.Value = Format(Left$(Target.Text, 1) & ":" & Mid$(Target.Text, 2, 2) & "." & Right$(Target.Text, 2), "@")
    End If
End Sub

Note that I've converted the number to text in m:ss.00 format because when I've specified the format to be "mm:ss.00", it was unavailable to convert it successfully. I'm unsure of the reason.

 

[rossbritton26]

Thanks for the help,

that code appears to work,

i tried entering times into column a, worked fine for rows 1:3 but for some reason stopped working from 4:6 then started working again??

How would i get it to display all five digits as it currently only displays the first four?? And would it be possible to only input four numbers, i.e seconds and milliseconds??

Thanks again,
Ross

 

[kpark91]

Thanks for the help,

that code appears to work,

i tried entering times into column a, worked fine for rows 1:3 but for some reason stopped working from 4:6 then started working again??

Hi, rossbritton.
If you're getting unknown results like "0:0...0" or anything like that you have format your cells as TEXT as it appeared to have worked like that on my spreadsheet.

How would i get it to display all five digits as it currently only displays the first four??

Get back after you've formatted the cells as Text.

And would it be possible to only input four numbers, i.e seconds and milliseconds??

Thanks again,
Ross

It is, and here is the code to do so

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.count > 1 Then Exit Sub
    
    If IsNumeric(Target.Value) Then
        Select Case Len(Target.Value)
        Case 4
            Target.Value = Format(Left$(Target.Text, 2) & "." & Right$(Target.Text, 2), "@")
        Case 5
            Target.Value = Format(Left$(Target.Text, 1) & ":" & Mid$(Target.Text, 2, 2) & "." & Right$(Target.Text, 2), "@")
        End Select
    End If
    Application.EnableEvents = True
End Sub

Also note that it does not recognize whether the time is out of range such as 5:61.30