1

I've got a lot of "duration" values - basically a race duration - in a format m:ss.millis [4:03.810 for example]. Currently GS handles it as text, but I would like to use those values for comparison and create some statistics.

Is this possible? I have read this: How to format a duration as HH:mm in the new Google sheets but even though I have created a custom formats like:

duration custom time format

or

custom time format

but neither with one nor with another I cannot use those values for calculations. GS always complains about the values beeing stored as text.

I guess I'm just doing something wrong, but I definitely need to be able to provide values in this format and be able to use them in calculations.

How can I do that?

Community
  • 1
  • 1
Krystian
  • 3,193
  • 2
  • 33
  • 71

1 Answers1

1

I regret that Duration seems to be a useless abomination and Sheets appears to offer no relatively easy way to convert text representation to values. Pending a better answer I suggest you convert all your durations as below:

=(left(A1,find(":",A1)-1)+right(A1,6)/60)/1440

format as Number:

mm:ss.000

and then apply your formulae.

(Change , to ; if required by your locale.)

A shorter formula might be used to cajole TIMEVALUE to work by including an hour value of 0:

=TIMEVALUE("00:"&A1)

A happy coincidence brought me back here because only very recently have I found that Google Sheets does offer a way to convert Text to Number (or I was having another aberration when I claimed otherwise). However, this still seems not to apply to Duration. Maybe there is yet hope though.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    Thanks a lot! I hope for something cleaner, but this is good enough for now. I will have to convert all of the values and copy them because running this formula all the time on the whole data set would kill the spreadsheet ;) – Krystian Apr 25 '17 at 07:19