Converting times between different lengths of course is a necessary evil in swimming, even though it is rarely accurate. Many competitions will accept converted times but care must be taken to use the correct conversion method to avoid confusion and worse disappointed swimmers when their entries are rejected for incorrect conversion.
British swimming have a published method, the Equivalent Time Algorithm written by Graham Sykes, to convert between many pool lengths which is documented here. However when I tried to implement it I found some steps in the conversion were missing. Chester-le-Street ASC have a much better write up here, but again I found some errors in the formulas (8/11/2024: which I must remember to let them know about!).
Using both the references above, and having worked out where the mistakes were, I have written Some Excel macros which perform the two most common conversion I need, 25m Short Course to 50m Long Course and 50m Long Course to 25m Short Course. These were then tested against several independent tools (The pullbouy.co.uk online converter, the Chester-le-Street ASC online converter here and the Sportsystems PC conversion utility). You are encouraged to do more testing and report any issues (or success) in the comments section below.
You can download an excel sheet containing these:
Please note that because this file contains macros both Windows and Office will warn you it could be dangerous, and you will need to tell windows the download is safe and enable editing and macros in Excel before they will work.
If you are not happy with the security of macro enabled spreadsheets from my random site on internet (and I wouldn’t blame you for being cautious), then you can examine the code for the macros which is included at the bottom of this page, and paste it into the Excel VBA editor yourself.
You may need to do the same to enable Macros.
To use the example
Enter the time to convert in the “From Time MM:SS.HH” box:
Click in the green “Event” box and select the stroke and distance:
Click in the green “Conversion” box and select the conversion:
Read the result from the “Converted time” box.
You can see the process of conversion:
1) Convert the MM:SS.HH time to decimal seconds with MMSSHH2dec()
2) Use either decLCM2SCM() or decSCM2LCM() to do the conversion depending on the stroke and distance.
3) Convert the decimal seconds back to MM:SS.HH with dec2MMSSHH()
Generally because Excel is so bad at understanding MM:SS.HH times it is much easier (and safer) to do all processing of swim times on decimal seconds (e.g. 1:23.45 is 83.45 seconds) and only use text formatted boxes for input and output of MM:SS.HH
There is a “Testing” tab where I have done a limited number of tests against the other converters mentioned above, you are encouraged to do more independent testing.
Here are the actual macros that do the conversion. You will note that the macros are really pretty short at ~20 lines of code each, most of them is comments to document how they work.
I will never claim to be an expert or knowledgeable VBA programmer, if you find a mistake or can offer any improvements to this code please do let me know.
Note you may also find the =MMSSHH2dec() and =dec2MMSSHH() macros from here useful, they are already included in the download above.
' ArrayFind
' A support function used by decSCM2LCM and deLCM2SCM
Function ArrayFind(value As String, arr As Variant) As Integer
Dim found As Integer, lb As Long, ub As Long, i As Long
found = -1
lb = LBound(arr)
ub = UBound(arr)
For i = lb To ub
If arr(i) = value Then
found = i
Exit For
End If
Next i
ArrayFind = found
End Function
' decSCM2LCM(decT25,evt)
' Convert swim times from SCM to LCM using the British swimming formula
'
' Times need to be in decimal seconds e.g. 123.45 represents 2 minutes 3.45 seconds
' Inputs: decT25 = decimal seconds time for the swim in a 25m pool
' evt = The stroke and distance from 50FR,100FR,200FR,400FR,800FR,1500FR,50BR,100BR,200BR,50FL,100FL,200FL,50BA,100BA,200BA,200IM,400IM
'
' Returns: Time in decimal seconds for the swim in a 50m pool
'
' The conversion is done with:
' Result =( T25 + sqrt ( T25^2 + 4 * PoolMeasure * TurnFactor * NumTurnFactor) ) / 2 * PoolMeasure
' however because the conversion is SCM to LCM (and not yards) some of the variables are constant: PoolMeasure = 1 & NumTurnPH = 3, so the formula simplifies to
' Result =( T25 + sqrt ( T25^2 + 4 * TurnFactor * NumTurnFactor) ) / 2
' Round result to nearest 0.1 seconds
'
' (C) March 2024 mark@leman.net / www.leman.net and released under MIT Licence https://opensource.org/licenses/MIT
' References https://www.swimmingresults.org/downloads/equivalent-time-share/algorithm.php and https://www.chesterlestreetasc.co.uk/wp-content/uploads/2019/02/Equation.pdf
' ***NOTE however both references have typos and omissions which make it very difficult to untangle the correct formula***
Function decSCM2LCM(decT25 As Double, evt As String) As Double
Events_list = Array("50FR", "100FR", "200FR", "400FR", "800FR", "1500FR", "50BR", "100BR", "200BR", "50FL", "100FL", "200FL", "50BA", "100BA", "200BA", "200IM", "400IM")
TurnFactor_List = Array(42.245, 42.245, 43.786, 44.233, 45.525, 46.221, 63.616, 63.616, 66.598, 38.269, 38.269, 39.76, 40.5, 40.5, 41.98, 49.7, 55.366)
Dim EventIndex As Integer
Dim TurnFactor As Double
Dim SwimDistance As Double
evt = UCase(Trim(evt)) ' Tidy up the 'event' string, force to upper and remove extra spaces
EventIndex = ArrayFind(evt, Events_list) ' Look up the 'event' index in the Event_list
If EventIndex = -1 Then ' If the event is not in the Event_list
decSCM2LCM = CVErr(xlErrValue) ' Return an error
Else ' else
SwimDistance = CInt(Left(evt, Len(evt) - 2)) ' Extract swim distance from event and convert to an int
TurnFactor = TurnFactor_List(EventIndex) ' Look up the turn factor from the table
NumTurnFactor = ((SwimDistance / 100) ^ 2) * 2 ' Constants simplify ((SwimDistance / 100) ^ 2) * (NumTurnPH - 1)
decSCM2LCM = Round((decT25 + Sqr((decT25 ^ 2) + (4 * NumTurnFactor * TurnFactor))) / 2, 1) ' Calculate the converted time
End If
End Function
' decLCM2SCM(decT50, evt)
' Convert swim times from LCM to SCM using the British swimming formula
'
' Times need to be in decimal seconds e.g. 123.45 represents 2 minutes 3.45 seconds
' Inputs: decT50 = decimal seconds time for the swim in a 50m pool
' evt = The stroke and distance from 50FR,100FR,200FR,400FR,800FR,1500FR,50BR,100BR,200BR,50FL,100FL,200FL,50BA,100BA,200BA,200IM,400IM
' Returns: Time in decimal seconds for the swim in a 25m pool
'
' The conversion is done with:
' DistanceTime = decT50 x PoolMeasure from table
' TurnValue = (TurnFactor from table / decT50) x (SwimDistance / 100)
' TurnTime = TurnValue x (ImperialDistance / 100) + (TurnsPerHundred -1)
' Result = DistanceTime - TurnTime
' Round result to nearest 0.1 seconds
'
' Because this function is converting SC to LC many values are constant
' PoolMeasure = 1
' TurnsPerHundred = 3
' ImperialDistance = SwimDistance
'
' So
' DistanceTime = decT50 x PoolMeasure
' Becomes:
' DistanceTime = decT50 x 1
' DistanceTime = decT50
'
' TurnTime = TurnValue x (ImperialDistance / 100) * (TurnsPerHundred -1)
' Becomes:
' TurnTime = TurnValue x (SwimDistance / 100) * (3 - 1)
' TurnTime = TurnValue x (SwimDistance / 100) * 2
' Insert TurnValue formaula in to TurnTime
' TurnTime = (TurnFactor from table / decT50) x (SwimDistance / 100) x (SwimDistance / 100) * 2
' TurnTime = (TurnFactor from table / decT50) x (SwimDistance / 100)^2 * 2
'
' And we end up with
' Result = decT50 - (TurnFactor from table / decT50) x (SwimDistance / 100)^2 * 2 )
' Round result to nearest 0.1 seconds
' (C) March 2024 mark@leman.net / www.leman.net and released under MIT Licence https://opensource.org/licenses/MIT
' References https://www.swimmingresults.org/downloads/equivalent-time-share/algorithm.php and https://www.chesterlestreetasc.co.uk/wp-content/uploads/2019/02/Equation.pdf
' ***NOTE however both references have typos and omissions which make it very difficult to untangle the correct formula***
Function decLCM2SCM(decT50 As Double, evt As String) As Double
Events_list = Array("50FR", "100FR", "200FR", "400FR", "800FR", "1500FR", "50BR", "100BR", "200BR", "50FL", "100FL", "200FL", "50BA", "100BA", "200BA", "200IM", "400IM")
TurnFactor_List = Array(42.245, 42.245, 43.786, 44.233, 45.525, 46.221, 63.616, 63.616, 66.598, 38.269, 38.269, 39.76, 40.5, 40.5, 41.98, 49.7, 55.366)
Dim EventIndex As Integer
evt = UCase(Trim(evt)) ' tidy up the 'event' string, force to upper and remove extra spaces
EventIndex = ArrayFind(evt, Events_list) ' look up the 'event' index in the Event_list
If EventIndex = -1 Then ' If the event is not in the Event_list
decLCM2SCM = CVErr(xlErrValue) ' Return an error
Else ' else
SwimDistance = CInt(Left(evt, Len(evt) - 2)) ' extract swim distance from event and convert to an int
TurnFactor = TurnFactor_List(EventIndex) ' Look up the turn factor from the table
decLCM2SCM = Round(decT50 - (TurnFactor / decT50) * (SwimDistance / 100) ^ 2 * 2, 1) ' Calculate the converted time
End If
End Function