When I export data from Garmin Connect web (https://Connect.Garmin.com) , the duration data of the activity becomes a time stamp. For example, i worked out for 1 hour and 5 minutes. In the export excel file this becomes a timestamp of 01:05AM. This is clearly incorrect.
Could you please suggest and workaround and (for the web developer: improve the export process)?
thanks
Ton
Are you talking about "Export CSV Splits"? (I don't see any other type of CSV export.)
In that case, if you look at the exported CSV file in a text editor, you'll see that activity duration (and other durations and paces) is exported like "34:10" (including the quotes).
When I look at these files in Excel, durations and paces are displayed as expected in the body of the sheet. It's only in the formula bar that they appear as timestamps. The reason for this is pretty simple - afaik, Excel doesn't actually have a separate data type for durations. All durations are actually timestamps internally, and they're only displayed like a duration based on the display format (which is automatically determined, by default).
If some or all of your durations are actually showing up in the sheet body as timestamps, it could be that Excel is guessing wrong when it decides to determine what display format to use.
Here's an example from one of my activities.
Text editor:
Excel (Office 365, Windows):
Excel (Office 365, Mac):
Long story short, there's nothing (*) Garmin can really do about this (unless they provided an option to export to an actual Excel file, like XLSX, which they won't).
You can work around this as follows:
- select the affected cells (containing a time or pace that doesn't display properly)
- right click the selection and choose "Format Cells"
- select Custom
- for cells that contain 2 colons (:) or a dot (.), select mm:ss.0
- for cells that contain 1 colon and no dot, select h:mm
Yes, it sucks that Garmin doesn't export times/paces in a uniform manner (as far as Excel is concerned.) You will see this if you actually try to do math on the data as given - it won't work properly if you mix cells with 1 colon and cells with 2 colons or a dot.
It actually is the case that if a "date/time/duration" (with one or two colons) has one colon and no dot, then Excel interprets that as hours and minutes, whereas if it has two colons or a dot, it interprets that as hours (if two colons are present), minutes and seconds.
e.g. 5:23 is interpreted as 5 hours and 23 minutes, but 5:23.0 is interpreted as 5 minutes and 23 seconds
(*) So I lied, they could do something and change the export format to be a bit more consistent, but that probably still wouldn't solve your problem. I feel like they only want to add the ".x" suffix when the precision of the data warrants it, but there's nothing preventing them from adding a "0:" prefix when only minutes and seconds are present.
