Tracking Swimming Times

[Coyneee]

Hi guys, I'm creating a swimming logbook for my swim team and I want to have a worksheet to log their personal best times for the year.

Formatting time can be a bit painful for me. I think I have it for the 100m, 200m, 400, 800m and 1500m races: [mm]:ss.00

However, for the shorter distance, 50m it won't work. I need to track just the seconds and the hundredths of a second. eg: 25.67 seconds. Can anyone please assist in the correct formatting?

Thanks

 

[bebo021999]

Test with cell A1:
Ctrl-1, custom format, Type:
[ss].00

input: 00:00:25.67
Output: 25.67

Is it are you looking for?

 

[Coyneee]

Test with cell A1:
Ctrl-1, custom format, Type:
[ss].00

input: 00:00:25.67
Output: 25.67

Is it are you looking for?

Thanks @bebo021999 this is exactly what I am after, however, given there will be many people entering data into this sheet, I would like the input to be simply 25.67, and not the 00:00:25.67. Is this possible?

Thanks again, really appreciate your expertise.

 

[bebo021999]

two options:
1) Type 25.67 in A1 (helper), A2 will be 25.67 in second format
2) using VBA to convert input into second'
which one do you refer to?

 

[Coyneee]

Hi @bebo021999, VBA if possible? Although I'm not too experienced with VBA.

 

[bebo021999]

Which range are input time range?
Assuming A1:A10

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target.Value = Target.Value / 86400
    Target.NumberFormat = "ss.00"
    Application.EnableEvents = True
End Sub

Input 25.27 in cell A1, it displays 25.27, but actual value is 0.000292477 (fraction of 1 day, with 1 second = 1/24/60/60)