Duration on a run (Read 51 times)

dfmurphy

baldmanrunning

posted: 12/10/2022 at 7:41 PM

If I copy/paste from the "workouts" table, the times will paste weird into Excel. This is due to the time not being consistent. If it's over an hour, it's h:mm:ss, but if it's under an hour then it's mm:ss. See table:

7.5 mi	2:04:57
2.5 mi	43:25:00

How difficult would it be to get time data normalized to be consistent? That would make manipulating data in an Excel sheet much easier.

Thanks!

milktruck

an amazing likeness

posted: 12/10/2022 at 8:43 PM
modified: 12/10/2022 at 8:47 PM

You're actually getting 'helped' by Excel trying to help out and convert the duration from the RA log into a time-of-day...but one can use the flexibility of Excel to get it all sorted out.

What I do to standardize the workout duration is flip them to seconds, or minutes:

1) since Excel treats the hh:mm:ss text from the workout duration as a time-of-day past midnight, format the column as Custom > [h]:mm:ss (ie Excel treats the duration as a fraction of a day, which in Excel is 86400 seconds)

2) multiply by 86400 to get to seconds duration

3) divide by 60 to standardize to minutes

Here is an example from a workout entry:

(date)	(activity)	(type)	(course)		(distance)	duration	pace
12/10/2022	walk	road	xxx		6.1 mi	1:15:16	12:21

Note the RA workout duration is "1:15:16" which is 1h 15m 16s. In Excel (you note you use cut-paste), this will be parsed as 1:15:16AM. Applying the 3 steps from above to the duration column, we can build two new columns

date	activity	type	course	dist	duration	pace	duration in seconds	duration coverted to min
12/10/2022	Walk	Road	xxx	6.1 mi	1:15:16	12:21	4516	75.27

So in my illustration here, the 1h 15m 16s workout is converted to 75.27 minutes.

All this is pretty quick and easy in Excel, but it sure does seem complicated to describe. Hopefully this is something which will help...

_{Acceptable at a dance, invaluable in a shipwreck.}