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

  • Thread starter Thread starter Sholto
  • Start date Start date
S

Sholto

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.
 
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.
 
Assuming all the entries in question follow the same format: 'mm:ss

Try this...

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

Then format the cells in the Time format of your choice.
 
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
 
Back
Top