Converting textdata from strava/zapier to use in calculations

Question

I have a zap @ Zapier which automatically imports my Strava activity to Google Sheets. This works fine, but it inputs everything as text. I want to be able to calculate my running (accumulated) average pace, but it is not possible with the data as is. So, do any of you have any formulaes for converting the info in column G to proper google sheets time format so that I can use it for calculations?

Here is a link to the data: https://docs.google.com/spreadsheets/d/1UD25--vrVgVp0AuhLFi7CGWFIo-GHeLfD_n8WqDbNrE/edit?usp=sharing

Answer 1

Try this formula in any blank column on row 2.

=(value(left(G2;len(G2)-3)) + value(right(G2;2))/60)/60/24

This parses the time value, eg 2:34, in column G, extracting the minutes from in front of the colon, and the seconds from after the colon, converts them from text to numbers, adds them together, and divides by 60 to get hours, and by 24 to get fractions of a day. Is this what you want?

For a formula that will do the whole column, place the following in row 1 of a blank column, and format the column for Time:

={"Time";arrayformula((value(left(G2:G;len(G2:G)-3)) + value(right(G2:G;2))/60)/60/24)}

Let me know if you need something else.