I have imported the .csv file into Excel I'm trying to figure out a formula to change the info in the "duration" and "pace" column into hours:minutes:seconds. Anyone know how to do this?
Making sense of downloaded data
(15 posts)-
Posted 8 months ago #
-
Here's the formula for the duration, assuming column "D" is duration...
=TIME(0,INT(D2/(60*1000)),MOD(D2/(60*1000),INT(D2/(60*1000)))*60)
Here's the formula for the pace (in km/s), assuming column "F" is pace...
=TIME(0,INT(F2/60),MOD(F2/60,INT(F2/60))*60)
Format both as 24-hour time. My Excel has a preset for "13:30:50".
Posted 8 months ago # -
Thank you! I knew the formula would be complicated, but ... wow.
The duration formula is perfect, but the pace column seems too fast for me; I'm getting times of between 5 and 6 minutes. Is it calculating for miles or kilometers? I think so, so I multiplied the pace column by 1.61 and adjusted the pace formula to work off of the new, higher pace number. This produced a pace that is exactly what I was expecting. Do you think my adjustment was correct?
Again, thank you!
Posted 8 months ago # -
Spot on, twmayben. It is in kilometers per hour, so your modification is correct.
I can't test on this computer, but I think this would be a single-column version for pace in mph...
=TIME(0,INT(F2*1.61/60),MOD(F2*1.61/60,INT(F2*1.61/60))*60)
Posted 8 months ago # -
I've simplified the calculations, if only slightly.
Here's the formula for the duration, assuming column "D" is duration...
=TIME(0,INT(D2/(60*1000)),MOD(D2/(60*1000),1)*60)
Here's the formula for the pace in kmph, assuming column "F" is pace...
=TIME(0,INT(F2/60),MOD(F2/60,1)*60)
Here's the formula for the pace in mph, assuming column "F" is pace...
=TIME(0,INT(F2*1.61/60),MOD(F2*1.61/60,1)*60)
I've still got a hunch that there's an easier way to calculate these, though.
Posted 8 months ago # -
I thought pace was reported in time per distance, e.g. minutes per kilometre or minutes per mile. For example, for me, my pace is around 4:28 per kilometre. To convert to miles, just multiply by 1.61 so my 4:28/km becomes 7:12/mile.
Posted 8 months ago # -
Thanks ATJ777! You made me think about it more and made the calculations even easier!
The CSV output returns the duration as number of milliseconds (eg 1931961) and the pace as seconds per kilometer (eg 505.78867450323). There are two challenges presented: 1) calculate the duration and pace to familiar terms, and 2) display it in familiar terms.
The raw calculation for duration could be the duration # divided by 1000, resulting in terms of seconds; pace is already in seconds per kilometer (just multiply by 1.61 to get seconds per mile) . Next, use Excel's TIME function to convert to a familiar time. I tried using =TIME(0,0,D/1000) for duration and =TIME(0,0,F) [or =TIME(0,0,F*1.61) for seconds per mile] for pace, but ran into a problem in extreme conditions. The TIME function will only accept values up to 32767 so I've added an asterisk to these simpler calculations.
Here's the formula for the duration, assuming column "D" is duration...
=TIME(0,0,D2/1000)
*durations longer than 9 hours, 06 minutes, 07 seconds will result in an error; in this situation use the following...
=TIME(0,INT(D2/(60*1000)),MOD(D2/(60*1000),1)*60)
... which gets unpredictable beyond 24 hoursHere's the formula for the pace for a kilometer, assuming column "F" is pace...
=TIME(0,0,F2)
*paces slower than 9 hours, 06 minutes, 07 seconds per kilometer will result in an errorIf you prefer your pace in km/h...
=F2/60Here's the formula for the pace for a mile, assuming column "F" is pace...
=TIME(0,0,F2*1.61)
*paces slower than 5 hours, 39 minutes, 12 seconds per mile will result in an errorIf you prefer your pace in mph...
=F2/60/1.61Posted 8 months ago # -
Hi there - can one of you smart people tell me how to import Nike+ data into Excel? I see the "save CVS" button on my slowgeek page and it brings up a very nice looking page of data when I click on it, but I can't figure out how to save it as anything but a text file, and I can't seem get that into Excel so that each piece of data is in it's own cell.
Thanks in advance,
UCMPosted 7 months ago # -
Open excel, then click file, open, then open the file. Specify that the "," is the delimiter.
Posted 7 months ago # -
Thank you so much, I knew it had to be something really simple like having no idea what a "delimiter" is! Anyway, It worked like a charm so I now have all my data neatly in Excel. Now, if only I had the Excel skills to do something with it...
Posted 7 months ago # -
"UncoolMom" what would you like to do with it? I can assist if you tell me. I wrote a script to convert my file to show mph, minutes per mile, calories burned, etc.
Posted 7 months ago # -
thanks for the offer, 4U2C, but part of the problem is that I don't really know what I want to do with it! I think what I'm really after is some magic formula that will motivate me to lose some weight and gain some endurance, which probably means I just need to quit playing around on the computer and go running more, but what's the fun in that?
Posted 7 months ago # -
Well, I started by graphng my runs to see my average pace increase, as well as my distance. Motivates me when I feel I havent made any progress during the year.
Posted 7 months ago # -
Thanks for this: very useful. But I cannot see where I can download the .csv file from...any ideas?
Posted 4 months ago # -
Look below the map next to your runs. There is a blue file icon. Click on this to export your runs.
Posted 4 months ago #
Reply
You must log in to post.