arithmetic on minutes and seconds without leading zero

[Christopher Glaeser]

I have a track and field worksheet and want to do arithmetic on sprint times
expressed as mm:ss or mm:ss.0. I can get it to work if I enter the times as
0:mm:ss, but entering the leading zero is tedious. Is it possible to enter
the times as mm:ss without the leading zero and get it to work as desired?

Best,
Christopher

 

[Shane Devenshire]

Hi,

Here is one possibility: Suppose the range where you will be entering the
times is A1:A10, and instead of entering 1:02 for 1 minute and 2 seconds,
enter 1.2

The code below will convert this to 0:01:02. It will not handle times
greater than 0:59:59 you will need to revise it if that is the case.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim center As Integer
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1:A10"))
If Not isect Is Nothing Then
On Error GoTo ErrorHandler
center = WorksheetFunction.Find(".", Target)
Application.EnableEvents = False
Target = "0:" & Left(Target, center - 1) & ":" & Mid(Target, center
+ 1, 10)
End If
ErrorHandler:
Application.EnableEvents = True
End Sub