Working in Excel with MM:SS.00 swim times (part 1)

Excel is an amazing tool and is used and abused to perform many tasks it was not originally intended for. One thing it should be good at is calculating times, however in swimming we tend to use times in the format minutes:seconds.hundredths and Excel does not really work well with times in this format.

If you search the internet you will find loads of posts saying you can enter a MM:SS.00 time in excel using the custom number format:

However as soon as you click on a time using this format to edit it, it looses the hundredths! This is not great when you have carefully typed in a load of times to work with and excel promptly corrupts them 🙁 . This is really frustrating.

Currently my best tip here is to enter the MM:SS.00 times as text, use an excel formula to convert the text to decimal seconds. Then to do any maths and convert back to MM:SS.00 to display.

For example 2 mins 45 seconds 30 hundredths becomes 165.30 decimal seconds, excel understands decimal numbers and can easily add, subtract, compare and manipulate them 🙂

Here is an example: In Cell A1 enter 2 min 45 seconds 30 hundredths, do this by typing ‘2:45.30 (the leading ‘ tells excel it is text).

in Cell A2 cut an paste the following excel formula:

=IFERROR((IFERROR(LEFT(A1,FIND(“:”,A1)-1)* 60,0) ) + RIGHT(A1,LEN(A1)-FIND(“:”,A1)),VALUE(A1))

Which will convert the contents of Cell A1 from text in ‘MM:SS.00 format to decimal seconds

To convert back from decimal seconds to MM:SS.00 cut an paste the following in Cell A3:

=IF(INT(A2/60)>0,TEXT(INT(A2/60),”0″&”:”),””)&TEXT(MOD(INT(A2),60),”00″)&”.”&TEXT(100*(A2-TRUNC(A2,0)),”00″)

These formulas have worked well for me for several years, although the excel formula are not not ideal to work with because of how many times they reference the original cell, which makes them hard to move.

Here is a sample excel sheet to demonstrate these in use:

Recently I have been working to automate the generation of the Qualifying times for the Kent County Championship and have spent some time making some easier to use VB code in excel to do this. Also code to convert from SC to LC using the British swimming method. More of this in a later post….

Using ‘Club Rankings’ to fetch Gala results and import in to Team Unify

I have spoken to many clubs who use Team Unify and don’t know that there is an easy way to get a results file for any gala their club members compete in, ready to import in the Team Unify.

The freely available ‘Club Rankings’ program from Sports Systems has an option to save a results file in .sd3 format that Team Unify can import.

To use Club Rankings you need to apply for a licence key and then download and install it.

The first step is to get an registered committee member from your club to email  club.rankings@swimmingresults.org and ask for a licence key. Details can be found on the main https://www.swimmingresults.org/ page, but it is amazing how many clubs have not noticed this useful information.

Unfortunately it can take some time to get a response, if you don’t hear back in 7 days try again and after another 7 days politely email rankings@swimming.org and ask them to chase for you. Generally this works. If not then get in touch, there is a small group of us who support each other using Club Rankings and we can try to help.

Once you have received your link to download and install Club Rankings and entered your licence key, you should find the screen below. Click on ‘Meets’ on the menu.

After a few seconds a list of all the meets any swimmers from your club has entered will appear. This is generated from rankings so all galas, even if entered privately by just one swimmer, will be shown.

Use the Export menu to save a results file for a meet.

The results files are saved in .sd3 format ready to import in to Team Unify.

Hope that helps!

Using TeamUnify to see who in your club has qualified for the Kent county swimming championships 2019

If your club uses TeamUnify and if you have uploaded the gala results for your club in to TeamUnify, then you can produce a report which shows who has qualified for the Kent champs.

If you have not uploaded your gala results I will do another post on how to get all your clubs results from rankings and import them into TeamUnify.

Here is how see who has qualified:

Login to your TeamUnify, select “Team admin”, then “time reports” and “top times by athletes”:

A windows pops up, in the window select:

  • “L&S->S” (Long & Short course converted to short course times) or  “L&S->L” (Long & Short course converted to long course times). They sometimes give slightly different results, for reasons discussed below.
  • If when you imported your results you gave each gala a meet type that matched it Licence Level, then select meet types of L1,L2,L3 & L4 (non licenced meets don’t count for qualification to county champs).
  • Select the period “1/1/18”  to “8/1/19” which is the qualifying period in the Kent conditions.
  • Check the ‘Age up’ is set to 31/12/2019 (this is American for ‘Age as at date’)
  • Show Top 1 entries
  • Select the “2019 Kent Championships (31/12/2019)” time standard. The date in brackets is the Age up date, not the competition date.

And Click Report Now…..

The report shows each swimmers PB and if it is a ‘Consideration’ or ‘Auto’ qualifying time for Kents.

Now you can go and remind the swimmers to enter. Unfortunately you still need to do the entries using the Sportsystems entry manager as TeamUnify does not work with Sportsystems meet files but at least you have a list of who to chase up.

Oddities and quirks

I found that “Long&Short course -> short” or “Long&Short course -> Long” sometimes gave  a different number of ‘Consideration’ and ‘Auto’ times when I counted them. I eventually found out where the differences came from:

  • When TeamUnify entered the Kent QTs as a time standard they made a few mistakes. (28-11-2018) I emailed TeamUnify with details and they will update the time standard with a few days.
  • When converting SC&LC->SC, if the LC time converted to SC exactly matches an existing SC time it appears to include both. e.g:
    1 100 Back 1:20.89L 1:19.90S Cons P 27/01/2018 Kent County Championships 2018
    1 100 Back 1:19.30S 1:19.30S Cons F 03/03/2018 OOJS EDSC Spring Charity Meet
  • There was one last anomaly I have not yet figured out but again it just told me the swimmer had qualified twice for the same event, which is no great disaster. e.g:
    1 50 Fly 33.45L 32.90S Cons P 11/02/2018 Kent Swimming Championships 20
    1 50 Fly 32.79S 32.79S Cons F 30/06/2018 RTW Monson Summer Splash Level