Forums >Suggestions and Feature Requests>time stamps
baldmanrunning
When I export data into Excel, I usually have to do a fair amount of gymnastics on timestamps.
If it's less than an hour, duration looks "mm:ssx" but when it's over an hour it's "hh:mm:ss" ... that confuses Excel (and me).
I'd like the option to force all timestamps into a single format. I'd rather have an activity less than an hour look like "00:mm:ss.000" ...
And if the hour count would go over 24 hours (which happens in ultras), I'd rather have it just keep adding hours instead of flipping the counter at 23:59 ... "29:12:34.567" is better than "1:5:12:34.567" ...
not bad for mile 25
I think Excel is doing that to you, not RA. I just exported mine in tab format to check, and a couple of examples that I see are these:
0:20:12.61
and
1:04:16.76
h:mm:ss.ss for both. Excel is trying to be smart and not succeeding. You might have to specify that any time columns are imported as text. After that, you can perhaps add a calculated column that makes the conversion correctly.
Pasting into Excel is part of the problem, but it's exacerbated by the inconsistency of the duration field in RA's "Workout" report.This is what I have:
If there's a way to force a leading "0:" then I'll have what I'm looking for.
It's less of a pain to parse out, but dropping the "mi" (or making it available as a separate column) would another request.
To clarify, the leading zero is there if you use "training log/tools/export" ... It is not in the "training log/workouts" ... that would be nice.
The export also put the unit in a separate column, whereas the workouts does not.
To clarify, the leading zero is there if you use "training log/tools/export" ... It is not in the "training log/workouts" ... that would be nice. The export also put the unit in a separate column, whereas the workouts does not.
Oh, gotcha. I hadn't thought of copying/pasting from that page.
Maybe this could help. In a new column to the right of the Duration column you could have this formula:
=IF(LEN(F13) - LEN(SUBSTITUTE(F13, ":", "")) < 2, CONCAT("00:", F13), F13)
...that prepends 00: if the cell to the left (F13 in this case) contains only one colon.
You still need to have the F column formatted as text.