4m 46.1s to seconds only

[paulfitz320]

has anybody got a formula to convert a time in the format 4
46.1s to a seconds only format in MS Excel

cheers

 

[Peo Sjoblom]

I doubt it's a time format, rather text if it includes m

=(4*60+46.1)/86400

format custom [ss].00

 

[paulfitz320]

Hi Peo

its actually a column of times in the General format (below) that
want to convert into seconds only, do you have a formula?

thanks

3m 50.9s
4m 7.1s
3m 46.5s
3m 59.8s
3m 55.6s
4m 6.5s
3m 58.3s
4m 1.8s
4m 6.3s
4m 8.2s
4m 7.4s
4m 7.4s
3m 56.1s
4m 4.3s
3m 51.7s
3m 48.1s
3m 58.5s
4m 1.8s
4m 0.5s
4m 10.8

 

[firefytr]

*...a time in the format 4m 46.1s to a seconds only format i
MS Excel...*

if it's typed in exactly like '4m 46.1s', then...

=(LEFT(B16,1)*60)+(MID(B16,4,4))

where B16 houses your product. if it's typed in differently w/
different format, this probably won't work for you.

HTH

-zac

 

[paulfitz320]

thanks zack that works with 4m 56.1s but not with 4m 6.1s, I need to pu
a zero before the 8 which is time consuming as I have a lot of data
but it solves half my problem, any suggestions for 4m 6.1s

 

[firefytr]

try...

=LEFT(D15,1)*60+SUBSTITUTE(SUBSTITUTE(RIGHT(D15,5)," ",""),"s","")

where D15 houses your product.

HTH

*edit*: only works w/ 1 digit as minute

 

[Norman Harker]

Hi P!

Try:
=(LEFT(A1,FIND("m",A1)-1)*60)+LEFT(MID(A1,FIND("
",A1)+1,99),LEN(MID(A1,FIND(" ",A1)+1,99))-1)

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[JE McGimpsey]

One way:

=TIME(0,LEFT(A1,FIND("m",A1)-1), ROUND(MID(LEFT(A1,FIND(".",A1)+1),
FIND(" ",A1)+1,255),0))