Forum Discussion
Using Excel to track and calculate Swimming times
I am a swim coach and I am trying to create a table in which i can record times for swimmer for each length of the pool that they swim. I then want to subtract the times between two lengths to get a split time for each lane. I need to have mm:ss.00. I have figured this out for anything less than 1 minute. Once I need to enter minutes my formulas fail. I am using a time format of mm:ss.000. If I enter 0:32.7 in one cell and 0:16.20 in a second cell then subtract them I get the correct answer of 16.5 in a new cell that is formated as a number.
2 Replies
To track and calculate swimming times in Excel, including handling times over one minute and calculating split times correctly, you can use the TIME and TEXT functions along with proper formatting. Here is a step-by-step guide to help you set this up:
Step-by-Step Guide
- Format the Cells for Time:
- Select the cells where you will enter the times.
- Right-click and choose Format Cells.
- Go to the Number tab, select Custom, and enter the format mm:ss.000. This format will handle minutes, seconds, and milliseconds.
- Entering and Calculating Times:
- Enter your times in the formatted cells using mm:ss.000 format. For example, enter 0:32.700 for 32.7 seconds and 0:16.200 for 16.2 seconds.
- Calculating the Split Time:
- To subtract one time from another and get the split time, simply use a basic subtraction formula. Excel will handle the time calculation correctly.
Example:
- Assume A1 has the time 0:32.700.
- Assume B1 has the time 0:16.200.
- In C1, enter the formula =A1 - B1.
4. Formatting the Result:
- Ensure the result cell C1 is also formatted as mm:ss.000 to display the split time correctly.
Example Implementation
Here's how you can set up the table:
Swimmer
Length 1 Time
Length 2 Time
Split Time
Swimmer 1
0:32.700
0:16.200
=A2 - B2
- Format columns B, C, and D with the custom format mm:ss.000.
Handling Times Over One Minute
If you need to handle times over one minute, the same approach applies. For example, if you need to enter 1:15.300 for one minute and 15.3 seconds:
- Enter 1:15.300 in the cell formatted as mm:ss.000.
- Subtract it from another time in the same format to get the split time.
Final Example with Over One Minute
Swimmer
Length 1 Time
Length 2 Time
Split Time
Swimmer 1
1:15.300
0:32.700
=A2 - B2
In C2, enter =A2 - B2 and ensure C2 is formatted as mm:ss.000.
Summary
- Use custom time format mm:ss.000 for all time entries and results.
- Subtract times directly to get the split times.
- Ensure result cells are also formatted as mm:ss.000.
This approach will correctly handle time calculations, including those over one minute, and display the results in the desired format. The text and the steps were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- Where do the TEXT and TIME functions fit in? I do not see them in your example.
