Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
arithmetic on minutes and seconds without leading zero
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
arithmetic on minutes and seconds without leading zero
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 -- If this helps, please click the Yes button Cheers, Shane Devenshire "Christopher Glaeser" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change a minutes and seconds as a 2400 time to seconds? | Excel Worksheet Functions | |||
Formula to Change Hours:Minutes:Seconds to Seconds only | Excel Discussion (Misc queries) | |||
custom number- minutes:seconds.hundredths how to hide a leading z | Excel Discussion (Misc queries) | |||
Convert "Time Interval" in "hours : minutes : seconds" to seconds | New Users to Excel | |||
Convert seconds to minutes and seconds in excel | Excel Worksheet Functions |