Recording Swimming Splits

Posted on by Rob van Gelder

I was talking with a workmate of mine and he mentioned that he is a swimming coach.
He told me that it’s difficult to record swimming times in Excel.
His approach was to record Minutes, Seconds and Splits (Split Seconds) as separate columns. Now, I can only imagine what sort of frustrating (if not impressive!) formulas this led to.

I explained to him that Excel can store Split Seconds right out of the box.
Just format the cell as mm:ss.00

Great! That’ll work.

In addition to simplifying time recording, investing in dedicated swimming pool management software like SwimClub Manager can streamline various aspects of running a swimming program.

From managing finances to scheduling swim meets and coordinating team activities, such software offers comprehensive solutions tailored specifically to the needs of swimming coaches and clubs.

By centralizing all aspects of swimming club management, SwimClub Manager not only saves time but also enhances the overall efficiency and organization of the coaching process.

Being the thinker, he suddenly realised typing in the times would be even more difficult than before. It’s that fiddly colon key :

“Is there a way to type the times with a decimal point as a separator?” he asked.

Fair enough question. His times on paper are written as dot separated.

Fun! Let’s write a formula!

I figured out there were 3 formats of time:
23 which means 23 seconds
23.45 which means 23.45 seconds
1.23.45 which means 1 minute and 23.45 seconds
The hour portion of the time is never reached.

So for a time typed into A1, the following formula turns it into an Excel time.
=IF(LEN(A1) – LEN(SUBSTITUTE(A1, “.”, “”)) = 2, TIMEVALUE(“00:” & SUBSTITUTE(A1, “.”, “:”, 1)), TIMEVALUE(“00:00:” & A1))

Again, the cell has a custom format of mm:ss.00

30 thoughts on “Recording Swimming Splits”

  1. Rob,
    I can only echo what you said earlier: I can only imagine what sort of frustrating (if not impressive!) formulas this will lead to ;) It seems to me that Excel and VBA only support datetime data to one second accuracy e.g. the Second function returns an integer, there is no Millisecond function, DateDiff has no millisecond interval argument value, etc. Entering datetime data in Excel to a greater accuracy means the data may no longer be used by built in functionality and you are forced to roll your own temporal functions (eek!)

    Considering the underlying data type for datetime is double float, the milliseconds is getting a bit too far into the danger zone for my liking. I think I may prefer the separate columns approach, kind of like a custom data type using scaled integers (a bit like VBA’s Decimal data type) rather than relying on the ‘lower’ end of a double float to maintain accuracy. Personally, I would find your text-based custom temporal data type a pain, parsing out the elements, etc.

    This may be overkill for the accuracy required by a swimming coach but I hope you get the general point.

    Jamie.

    –

  2. I once wrote a small utility that shows you a userform with which you can edit times with three decimals.

    It is rather crude, but if anyone wants it I’ll put it on my site.

  3. I am an Excel amateur and found this site hoping for some help. I can’t figure out how to “post” my question so I’ll pose it here if that is OK.

    I am trying to automate a form so that by providing input to a particular cell (maybe via a checkbox), subsequent rows underneath will “unhide”. I’ve figured out how to unhide the rows using a Macro but the users who will be using this form I am creating will not understand how to use a Ctrl+key to perform this.

    Any help would be appreciated.

  4. KPM6700:

    I’m not quite sure what you’re after. A checkbox that when ticked will show/hide rows?

    From the Forms toolbar, create a checkbox in a cell, right-click it, assign macro, create new. Paste this code in:

    ‘—
        Dim shp As Shape, rng As Range

        Set shp = ActiveSheet.Shapes(Application.Caller)
        Set rng = shp.TopLeftCell.Offset(1).Resize(10)

        rng.EntireRow.Hidden = (shp.ControlFormat.Value = 1)
    ‘—

    These types of questions get answered very quickly in Newsgroups.
    A good starting point for newsgroups?
    Start at this page: Spreadsheet Internet Newsgroups

  5. Jamie –

    A simple function to convert mm.ss:00 into seconds might allow all existing formulas to be used.

  6. My problem is similar to some extent. I have a bunch of time figures formatted in mm:ss:00, and I’m looking to convert term to raw seconds (i.e. 1:12:34 = 72.34)

    Does anyone know how this could be done???

    thanks
    Chris

  8. Thanx Jon
    Have been breaking my head to make mm:ss.00 into ss.00 – have been searching for last 1 week. thanx a lot. i’m going to bookmark this site.

  9. hi,
    spoke too soon. mm:ss.00 converts OK. But problem still persists. I want the speed too – distance /time (in ss.00). This way it still divides the dist by mins & not secs shown. Have to manually convert all mm:ss.00. :-(

    Can anybody suggest a good way to do this?
    BTW
    =MINUTE(A1)*60+SECOND(A1) gives only ss & not the hundredth splits

  10. Hello,
    Found out the solution to get raw seconds – so that it can be further manipulated.
    AA) one way:
    Save the worksheet as text (Tab Delimited).txt file from Excel
    File >> Save As >> Text(Tab delimited).txt
    Open the file back in Excel.
    Downside? You have to save each worksheet & cannot save workbook in full.

    BB) second (& IMHO the best) way:
    Format the column where the splits are entered as mm:ss.00 (the original column)
    In the Next (Right) column use this
    =TEXT(A1, “[ss].00?)

    There… it is done.
    Thanx to all of you..

  12. Hi guys,

    I am a rowing coach and am putting the data that my crew gives me from their training sessions into a spreadsheet in the mm:ss.00 format. However, if I make a mistake inputing the data e.g type 1:23.03 when it should be 1:23.30 something weird happens.

    When I click in the cell, the value rounds up or down depending so 1:23.30 will round to 1:23.00. This will have a massive impact on the results that I am looking at. Is this a common occurrence in excel or is there something that I am doing wrong or should be pressing?

    Any help would be much appreciated

  14. Hello all,

    I am an English teacher tracking the reading times of 1st-graders in minutes and seconds. I want to use the data to make charts to show parents their child’s improved speed in reading a single passage repeatedly over time. I am using Excel for Mac 2004 with OS X Tiger (10.4.10).

    I have read the previous posts here and tried their suggestions, but no matter how I try to Format Number in my minutes & seconds column, and no matter how I enter the data, Excel converts everything to HH:MM AM/PM format in the formula bar, and displays it as a value that is the result of some kind of calculation in an XXX:00 format in the cell. For example, a reading time of 2’16? shows up as 2:16:00 AM in the value bar and as 136:00 in the cell. When I try to make a chart out of the data, Excel cannot make sense of the X and Y axes and gives me error messages that I am reduplicating my series values, and the lines in the graphs are all completely flat (no change up or down).

    I have tried all the format types, including mm:ss and mm:ss.0, but to no avail. I noticed that the User’s Manual listed them as [mm]:ss and [mm]:ss.0, so I edited them in the Custom Formats box, also to no avail. I have tried playing around with my X and Y axes, data series, and so on. Nothing has worked.

    I am beginning to wonder whether this is some kind of weird bug that Microsoft simply did not test thoroughly enough to catch. Otherwise, why wouldn’t the suggestions in the previous posts have worked?

    Does anyone have any suggestions for an easy way to get the charts I want for my parents? It seems I am either overlooking something obvious, or I need a creative solution. (Please note: I am a language person, not a math or computer person, so I probably won’t be able to make use of suggestions involving writing programming code or doing anything too deep or technical.)

    I will be really grateful to anyone who ventures a response. Thanks for your help!
    Bruce

  15. Bruce: You have to enter hours, minutes, and seconds. If you want to enter 2 minutes and 16 seconds, enter it as

    0:2:16

    This tells Excel all it needs to know.

  16. Jon: I have the same question as Bruce. I want to enter time as an elapsed time, but when I try your suggestion I still get a clock time with AM/PM. In my case I am using excel to log exercise training intervals, i.e. 1 min 48 sec 4 millisec to row 500 meters. Would be grateful for any further advice. Garrett

  18. I need to convert M:SS into just seconds. Example 3:00 would be 180 and 4:01 would be 241. I have searched high & low across the internet and not been able to find anything that helps me. I’ve tried different formats and formulas, nothing has helped thus far. Any suggestions would be appreciated.

  19. I’m all good with the mm:ss.00, however, excel converts the data into a date and time for instance 1:45.87 is shown in the edit bar as 12:01:46 AM, I don’t want a time, i want just pure minutes, seconds and tenths of seconds, did I miss something?

  20. Chuck –

    That is how Excel internally stores your racing split. Leave it the way it is in the formula bar, and rest assured that you can format it as necessary through the custom number format.

  21. Thanks Jon,

    Question, when I use that format and leave it as a date/time as excel formats it, I get errors when performing calculations that have negative numbers. I think it has something to do with a negative value and the time format. Any suggestions?

  22. Chuck –

    Unless you’re using the 1904 date system (and don’t), date formats cannot handle negative numbers. Do a check first, like

    =IF(A1>A2,A1-A2,A2-A1)

    Or put these into adjacent cells:

    =if(A1>A2,A1-A2,””)
    =if(A1>A2,””,A2-A1)

  23. Thank you for posting the date functions, they saved me some time and I like the logic. I found this formula for Memorial Day to useful.

    =DATE(YEAR($S$1),5,32-(WEEKDAY(DATE(YEAR($S$1),5,31),2)))

  25. Correction to the previous post. It should read
    =FLOOR(“5/”&DAY(MINUTE(YEAR(B1)/38)/2+56)&”/”&YEAR(B1),7)-34
    I had adjusted it to find Good Friday which is two days earlier.

    Sorry for the extra posts, feel free to consolidate.

  26. Hello all

    I just updated 2 of my Excel files:

    – a file with all major SWIMMING records: men/women – long course/short course – all distances – World/American/European/Oceania/African/Asian/Olympic/Championship/Belgian record. For each record: time/holder/nationality/city/country/date

    – a file with all major ATHLETICS records: men/women – outdoor/indoor – all events – World/American/European/Oceania/African/Asian/Olympic/Championship/Belgian record. For each record: time/holder/nationality/city/country/date

    Sources: various websites, including IAAF, FINA, Wikipedia, …

    I put it on my website, but the site is in Dutch, so feel free to email me if you want a copy via email. wimmekegielis@hotmail.com

    I also store the data in TM1 cubes (see http://www-306.ibm.com/software/data/cognos/tm1/); I am a BI consultant mainly working with TM1.

    Cheers,

    Wim Gielis
    AKA Wigi on various Excel boards

  27. Help!

    I too have a similar problem. I am making a graph to help my daughter see her 500 yard free style visually so she can see where she needs to pick up her time in order to reach desired cuts. I have broken the race up into 20×25 yard segments but I am struggling with the formats like many on this page. I could send the spread sheet to someone and use Snag It to show what I am trying to do…….if any of you math wizards here could please help me it would be greatly appreciated

  29. Pingback: Daily Dose of Excel » Blog Archive » Fractions of Seconds

  30. I’ve just done this using a different method.

    I put in all my split times in terms of ss.00 (e.g. lap 1: 25.20) etc, and didn’t really want to faff with changing it all.
    So, I’ve just got a couple more cells at the top of my times with:
    (B10) Total seconds =SUM(splits) – Total time in ss.00
    (B9) Total Minds =ROUNDDOWN(B10/60,0) – number of minutes it took
    (B8) Seconds =MOD(B10,60) – extra seconds in the last minute
    (B7) Total Time =B9+(B8/100) – minutes added to the last seconds>> Total time!

    No fancy formatting needed and a nice simple method. Hope that helps someone!

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.