Formatting text to mm:ss time

[Trillian]

I need to average some durations recorded in the format minutes:seconds. They were originally numbers formatted as text.

I have formatted the cells as mm:ss. I need a formula similar to =timevalue to convert the data to serials, but =timevalue gives me a 24-hour clock value, rather than minutes and seconds.

Any suggestions?

 

[Jonmo1]

try

=TEXT(A1,"mm:ss")

 

[Andrew Poulsom]

Like this?

=TIMEVALUE("00:"&A1)

 

[Trillian]

These methods give me the same result as using =timevalue with no other qualifiers. The fields look correct, but taking an average or finding the max or min value are wrong.

Is there a way to express my mm:ss values as fractions of a minute?

 

[Andrew Poulsom]

Sorry I don't understand.

If A1 contains the text entry 15:03, then:

=TIMEVALUE("00:"&A1)
=TIMEVALUE(A1)

return 00:15:03 and 15:03:00 respectively when formatted as hh:mm:ss.

To get decimal minutes use:

=TIMEVALUE("00:"&A1)*24*60

and format as General.

 

[Trillian]

I'm sure I'm explaining it badly. Sorry.

If I format using your method, it looks right. But if I try to average with =average(a1:whatever) or select the maximum value with =max(a1:whatever), both calculations fail. The average gives me a divided by zero error, and the max value comes up as zero.

 

[Andrew Poulsom]

You won't be able to perform arithmetic on the text entries. Put the formula I posted in column B and use that column as the reference in your AVERAGE and MAX formulas.

 

[Trillian]

I was using the computed values. I guess I've got a problem with my data, and I don't know what it is. If I try this with a small set of exaple data it works fine; with my actual sheet it doesn't.