Change Times

D

DaveM

Hi all

How could I change 1m 1.40s it's formatted as General, to read as time 61.40

Thanks

Dave
 
J

joel

You need to add a new column with a formula to do the conversion. I
you data is in cell A1 use this formula in cell B1

=(60*LEFT(A1,FIND("m",A1)-1))+MID(A1,FIND(
",A1)+1,(FIND("s",A1)-FIND(" ",A1))-1)


The formula extracts the 1 minutes and multiplies by 60 then extract
the 1.40 and adds it to the value 60.

The formula works by loking for the character m and extracts th
characters to the left of the "m". then the formula extracts th
characters between the 1st blank character and the "s" character.
 
D

DaveM

Hi Joel

Thanks for your reply

I get #VALUE! in cell B1 its not working.

I was also hoping it would work with other times like,

1m 0.32s
1m 2.15s
1m 5.52s
59.01s
58.77s
Etc.

Thanks InAdvance

Dave
 
J

joel

I'm not sure why you are getting an error. Maybe there is a blan
characters at the beginning or end of the string or your data isn't i
cell a1.


I modified the function to handle the case where there are no minutes

=IF(ISERR(FIND("m",A1)),LEFT(A1,LEN(A1)-1),(60*LEFT(A1,FIND("m",A1)-1))+MID(A1,FIND(
",A1)+1,(FIND("s",A1)-FIND(" ",A1))-1))
 
D

DaveM

Hi Joel

My apologies i've been away for three days, just got back.

That works fine.

Thanks for your posts.

All the best.

Dave
 
Top