How do I convert time as text 'mm:ss to time value in Excel
 

I have time stored as text in the format 'mm:ss (e.g. '25:15) Note
the leading apostrophe.
Every Excel function & format I try assumes the minutes are hours and the
seconds minutes. I can manually edit cell to 0:mm:ss to get the functionality
I need but have 2000 rows to edit...

Any ideas would be much appreciated.

How do I convert time as text 'mm:ss to time value in Excel
 

Try this:

=timevalue("0:"&right(a1,5))

Format as mm:ss

Regards,
Fred.

"Sholto" wrote in message
...
I have time stored as text in the format 'mm:ss (e.g. '25:15)
Note
the leading apostrophe.
Every Excel function & format I try assumes the minutes are hours and the
seconds minutes. I can manually edit cell to 0:mm:ss to get the
functionality
I need but have 2000 rows to edit...

Any ideas would be much appreciated.

How do I convert time as text 'mm:ss to time value in Excel
 

I think you can just do this...

=A1/60

and then format that as mm:ss

--
Rick (MVP - Excel)


"Fred Smith" wrote in message
...
Try this:

=timevalue("0:"&right(a1,5))

Format as mm:ss

Regards,
Fred.

"Sholto" wrote in message
...
I have time stored as text in the format 'mm:ss (e.g. '25:15)
Note
the leading apostrophe.
Every Excel function & format I try assumes the minutes are hours and the
seconds minutes. I can manually edit cell to 0:mm:ss to get the
functionality
I need but have 2000 rows to edit...

Any ideas would be much appreciated.

How do I convert time as text 'mm:ss to time value in Excel
 

That will work as long as the apostrophe shown by the OP is Excel's text
indicator. If there's actually a leading apostrophe in the cell, it results
in a value error.

Regards,
Fred.

"Rick Rothstein" wrote in message
...
I think you can just do this...

=A1/60

and then format that as mm:ss

--
Rick (MVP - Excel)


"Fred Smith" wrote in message
...
Try this:

=timevalue("0:"&right(a1,5))

Format as mm:ss

Regards,
Fred.

"Sholto" wrote in message
...
I have time stored as text in the format 'mm:ss (e.g. '25:15)
Note
the leading apostrophe.
Every Excel function & format I try assumes the minutes are hours and
the
seconds minutes. I can manually edit cell to 0:mm:ss to get the
functionality
I need but have 2000 rows to edit...

Any ideas would be much appreciated.

How do I convert time as text 'mm:ss to time value in Excel
 

Assuming all the entries in question follow the same format: 'mm:ss

Try this...

Select the range of cells in question
Goto the menu EditReplace
Find what: ' (enter an apostrophe)
Replace with: 0:
Replace All

Then format the cells in the Time format of your choice.

--
Biff
Microsoft Excel MVP


"Sholto" wrote in message
...
I have time stored as text in the format 'mm:ss (e.g. '25:15)
Note
the leading apostrophe.
Every Excel function & format I try assumes the minutes are hours and the
seconds minutes. I can manually edit cell to 0:mm:ss to get the
functionality
I need but have 2000 rows to edit...

Any ideas would be much appreciated.

How do I convert time as text 'mm:ss to time value in Excel
 

If that were the case (although I don't think the OP was indicating that),
then this should work...

=SUBSTITUTE(A1,"'","")/60

again, formatting the cell with mm:ss

--
Rick (MVP - Excel)


"Fred Smith" wrote in message
...
That will work as long as the apostrophe shown by the OP is Excel's text
indicator. If there's actually a leading apostrophe in the cell, it
results in a value error.

Regards,
Fred.

"Rick Rothstein" wrote in message
...
I think you can just do this...

=A1/60

and then format that as mm:ss

--
Rick (MVP - Excel)


"Fred Smith" wrote in message
...
Try this:

=timevalue("0:"&right(a1,5))

Format as mm:ss

Regards,
Fred.

"Sholto" wrote in message
...
I have time stored as text in the format 'mm:ss (e.g. '25:15)
Note
the leading apostrophe.
Every Excel function & format I try assumes the minutes are hours and
the
seconds minutes. I can manually edit cell to 0:mm:ss to get the
functionality
I need but have 2000 rows to edit...

Any ideas would be much appreciated.