Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Times
Hi all
How could I change 1m 1.40s it's formatted as General, to read as time 61.40 Thanks Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data change color when entered 3 times | Excel Discussion (Misc queries) | |||
Updated times stamp of last change | Excel Discussion (Misc queries) | |||
Change times to 24 hour and add 6 hours in strings | Excel Worksheet Functions | |||
Change event on single cell fires many times | Excel Programming | |||
Change range multiple times | Excel Programming |