Tracking Swimming Times

Coyneee

New Member
Joined
Mar 29, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Which range are input time range?
Assuming A1:A10
VBA Code:
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)
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top