This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

I export my activities by "splits to csv file", format of AVG.Pace (and some more) is hh:mm, how I fix it into mm:ss for edit by excel?

In column "time", format is mm:ss but In column  "pace", format is hh:mm. How can I convert pace into mm:ss?

  • This value is already exported as mm:ss. If you open the .csv file with a text editor, you can see it.

    The problem is that Excel doesn't recognize this format. Excel expects either hh:mm:ss or hh:mm or mm:ss.tenths (or mm:ss,tenths in countries like mine where , is the decimal separator). 

    So if you run at an average pace of 6:09, this is how it will look when imported into Excel:

    • "6:09" will be interpreted as 6 hours, 9 minutes.
    • "0:6:09" will be interpreted as 6 minutes, 9 seconds.
    • "6:09.0" (or "6:09,0" depending on your regional settings) will be interpreted as 6 minutes, 9 seconds, 0 tenths of a second

    I don't think there are any import settings in Excel where you can force it to parse as mm:ss. There is a setting in the .csv import wizard where you can force it to import as text, but that is it. 

    So you need to repair the .csv file. You can add the missing "0:" in front of the pace value. Or you can add a trailing ",0" or ".0" depending on your regional settings.

    You can do this yourself in a text editor. Or you can try to convince Garmin to change their .csv format into something less ambiguous.

     

  • Tks for reply, i understand what you said and i can edit with .csv file by manual in this case but I mean the format of “time” column is difference “pace” collumn, so i want there are same format. Pls export the newest activity and check. 

  • Yes, the "time" column has a trailing digit, just as in one of my examples above. But that isn't ideal either, because Garmin will skip the trailing digit if it is 0, and then you will again get a wrong import.

    So this is not the solution.

  • It's fairly annoying Gamin not getting the pace format correct. I found a simple solution though, add a second column, divide the pace time by 60, change the format to custom "mm:ss", then copy and paste special pack as "value", delete the original pace column. Done.