# Formatting text to mm:ss time

#### Trillian

##### New Member
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?

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### Jonmo1

##### MrExcel MVP
try

=TEXT(A1,"mm:ss")

#### Andrew Poulsom

##### MrExcel MVP
Like this?

=TIMEVALUE("00:"&A1)

#### Trillian

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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.

Replies
1
Views
308
Replies
6
Views
172
Replies
9
Views
225
Replies
1
Views
311
Replies
2
Views
315

1,191,516
Messages
5,987,034
Members
440,074
Latest member
Emmanuelian

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back