Change Times
in [Programming]
|
From: DaveM on 31 May 2010 03:04 Hi all How could I change 1m 1.40s it's formatted as General, to read as time 61.40 Thanks Dave
From: joel on 31 May 2010 05:39 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/showthread.php?t=206223 http://www.thecodecage.com/forumz
From: DaveM on 31 May 2010 12:58 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" <joel.4btl8u(a)thecodecage.com> wrote in message news:joel.4btl8u(a)thecodecage.com... > > 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/showthread.php?t=206223 > > http://www.thecodecage.com/forumz >
From: joel on 31 May 2010 22:39 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/showthread.php?t=206223 http://www.thecodecage.com/forumz
From: DaveM on 4 Jun 2010 15:44 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" <joel.4buwfh(a)thecodecage.com> wrote in message news:joel.4buwfh(a)thecodecage.com... > > 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/showthread.php?t=206223 > > http://www.thecodecage.com/forumz >
|
Pages: 1 Prev: Coloring Alternate lines Next: Replacing spaces with underscore for specific expressions |