Forum Discussion

Rowan950's avatar
Rowan950
Copper Contributor
Dec 11, 2023

Changing time of a race to seconds

Hi everyone,

 

I'm coaching a freshman rowingteam that will have a series of tests soon. We know the time per 500 meters (aka "splitt") which is usually written as 1:42,6 (so m:ss,0/minutes:seconds,decimals). I need to total amount of seconds to calculate the wattage they are excercising and at the moment it means that i'm creating multiple columns, write those numbers individually and use a formula to multiply it to seconds (implemented within the formula) where i would rather be able to just copy-paste the splitt in stead of creating multiple columns and fill them in manually to save myself some time. 

 

 

Do you have any tips for me? 

 

Sincerely,

Rowan

Formulas and Functions

2 Replies

Sort By
    • mtarler's avatar
      mtarler
      Silver Contributor
      Dec 11, 2023

      Rowan950  In Riny's solution that assumes you enter a "time" in the first column.  You can make sure Excel recognizes you input as a "time" value of mm:ss.0 by preceding it with 00: (i.e. 00 hours) or by always including the decimal part.  If you are copy and paste the text from somewhere else and it comes in as "text" then you can add TIMEVALUE("00:" & A1) to the formula to convert to a time (assuming you never have any hours.  So the formula for a text input would be:

      =ROUND(2.8/((TIMEVALUE("00:"&A1)*86400)/500)^3,0)

Resources