4m 46.1s to seconds only

  • Thread starter Thread starter paulfitz320
  • Start date Start date
P

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
 
I doubt it's a time format, rather text if it includes m

=(4*60+46.1)/86400

format custom [ss].00
 
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
 
paulfitz320 said:
*...a time in the format 4m 46.1s to a seconds only format i
MS Excel...*
Click to expand...

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
 
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
 
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
 
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.
 
One way:

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