1

time stamps (Read 36 times)

dfmurphy


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:ssTight lippedx" 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" ...

    LedLincoln


    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.

      dfmurphy


      baldmanrunning

        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:

         

        Distance Duration Pace
        2.0 mi 27:52 13:57
        6.3 mi 1:05:18 10:22

         

        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.

        dfmurphy


        baldmanrunning

          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.

          LedLincoln


          not bad for mile 25

            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.