Tricky one... for me anyway.

[jamieisrubbishwithexcel]

Hi all,

I was wondering if there was a formula so that I could convert the following into the desired format.

[TABLE="width: 122"]
<colgroup><col></colgroup><tbody>[TR]
[TD]12 mins 52 secs = 12.52

[/TD]
[/TR]
[TR]
[TD]4 mins 11 secs
= 4.11

[/TD]
[/TR]
[TR]
[TD]23 mins 35 secs
= 23.35

[/TD]
[/TR]
[TR]
[TD]2 mins 39 secs
=2.39

[/TD]
[/TR]
[TR]
[TD]29 secs
=0.29

[/TD]
[/TR]
[TR]
[TD]22 mins 33 secs
=22.33

Any help would be greatly appreciated.

Thanks


[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott Huish]

If the value is 1 does it still say mins or secs?

 

[jamieisrubbishwithexcel]

Hi Scott - Thanks for the reply.

It currently reads '1 mins 0 secs' and i'd like to convert it to 1.00

Thanks

 

[shg]

Slightly different,

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]12 mins 52 secs [/td][td]

12:52​

[/td][td]B1: =IFERROR(SUBSTITUTE(A1, " secs", "")/86400, --SUBSTITUTE(SUBSTITUTE("0:" & A1, " mins ", ":"), " secs", ""))[/td][/tr]

[tr][td]

2​

[/td][td]4 mins 11 secs[/td][td]

4:11​

[/td][td]Format of B1: [m]:ss[/td][/tr]

[tr][td]

3​

[/td][td]23 mins 35 secs[/td][td]

23:35​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]2 mins 39 secs[/td][td]

2:39​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]29 secs[/td][td]

0:29​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]22 mins 33 secs[/td][td]

22:33​

[/td][td][/td][/tr]
[/table]

 

[Roderick_E]

Assumes in cell E4, change for your purposes.

=IF(ISERROR(SEARCH("mins",E4))=FALSE,VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E4,"mins",".")," ",""),"secs","")),VALUE("."&SUBSTITUTE(E4,"secs","")))

 

[Rick Rothstein]

This formula seems to return the values you asked for (although I am not sure I like your use of a decimal point to separate minutes from seconds)...

=TEXT(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(FIND("mins",A1))," mins ","")&A1," mins ",".")," secs",""),"0.00")

 

[Rick Rothstein]

=SUBSTITUTE(SUBSTITUTE(IF(ISERROR(FIND("mins",A1)),"0.","")&A1,"secs","")," mins ",".")

The OP indicated he wanted 1.00 returned for "1 mins 0 secs" (which I assumed meant two decimal places with trailing zeroes where applicable) whereas your formula returns 1.0. Also, it looks like your formula is returning a trailing space as well.

 

[Scott Huish]

I deleted my post because it has the problem of it there are 1 digit seconds like 1 minute 7 seconds, it would return 1.7, not 1.07 which it should be. Yours returns 1.70. shg's formula returns what should be the correct result.

 

[Rick Rothstein]

I deleted my post because it has the problem of it there are 1 digit seconds like 1 minute 7 seconds, it would return 1.7, not 1.07 which it should be. Yours returns 1.70.

Good catch... I completely forgot to check for that (I sure wish I could delete my post right now as well). This formula would work to return the value the way the OP indicated he wanted...

=IF(ISERROR(FIND("mins",A1)),"0.",LEFT(A1,FIND(" ",A1)-1)&".")&TEXT(TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A1," ",REPT(" ",999)),2*999),999)),"00")

shg's formula returns what should be the correct result.

The only thing with Scott's formula is it returns a real Time value and I am not sure that is what the OP wanted or can work with in other parts of his worksheet/workbook. But, assuming the OP doesn't mind the vaue being a real Time value, but still wants the decimal point instead of the colon, then he can use this Custom Format instead of the one Scott suggested...

[m].ss

 

[jamieisrubbishwithexcel]

Hi all,

I'm happy to change to the colon and is what I would have used anyway so thank you all for your help with this matter.

I've now successfully implemented the formula,

Truly wonderful people!!!