Change Times
 

Hi all

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

Thanks

Dave

Change Times
 

You need to add a new column with a formula to do the conversion. If
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 extracts
the 1.40 and adds it to the value 60.

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=206223

http://www.thecodecage.com/forumz

Change Times
 

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






"joel" wrote in message
...

You need to add a new column with a formula to do the conversion. If
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 extracts
the 1.40 and adds it to the value 60.

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=206223

http://www.thecodecage.com/forumz

Change Times
 

I'm not sure why you are getting an error. Maybe there is a blank
characters at the beginning or end of the string or your data isn't in
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))


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=206223

http://www.thecodecage.com/forumz

Change Times
 

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



"joel" wrote in message
...

I'm not sure why you are getting an error. Maybe there is a blank
characters at the beginning or end of the string or your data isn't in
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))


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=206223

http://www.thecodecage.com/forumz