text in mm:ss to time

[P Boric]

_I have times in text format as mm:ss (eg. 03:45) and want to convert them to
time numbers in the same format.

 

[Rick Rothstein \(MVP - VB\)]

Do you mean you want to convert them in place? Select the cells containing
these "text times", click Data/Text To Columns on Excels menu bar and click
Finish on the dialog box that appears. You might have to then format the
cells to display the way you want.

Rick

 

[Hershey]

=TRIM(A1)*1
Format as mm:ss

 

[Jarek Kujawa]

insert 0 in some cell, copy, select yr data, Edit->PasteSpecial-

 

[P Boric]

Thanks, it works but with some "details" (I have tried before with similar
function CLEAN and got similar results):

If I use the mm:ss format I get the answer in the wrong order, (for instance
03: 25 I get 25:03 instead)
If I choose format hh:mm I get the right order but I don't know why
Furthermore in this last format, when a number is greater than 30 minutes
(for instance 46:25), I get displayed a different number as 22:25, but
internally is stored the right value, as when I add it othe time as 02:15 I
get the right answer 48:40
Very strange isn´t? I have checked the regional settings and seem are ok

 

[David Biddulph]

03:45 will not default to minutes and seconds, but to hours and minutes.

Hershey may therefore have intended not to say =TRIM(A1)*1 but
=TRIM(A1)/60
You may also want to format as [mm]:ss, rather than as mm:ss, in case your
number of minutes goes beyond 60.