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]

 

[jamieisrubbishwithexcel]

[TABLE="width: 122"]
<colgroup><col></colgroup><tbody>[TR]
[TD]1 hrs 8 mins 22 secs[/TD]
[/TR]
</tbody>[/TABLE]
ahhh,

I failed to spot the following... '1hrs 8 mins 22 secs' - Is there a formula which could work for all 3 different ways which the data is being displayed currently?

Again, many thanks in advance.

 

[Rick Rothstein]

I failed to spot the following... '1hrs 8 mins 22 secs' - Is there a formula which could work for all 3 different ways which the data is being displayed currently?

Whose solution did you finally decide to use for your original question?

How did you want that displayed?

Does it affect how you want the original results displayed?

 

[jamieisrubbishwithexcel]

Hi Rick,

I went with SHG's formula as it presented the data with the colon.

I would like to display it as '01:08:22' if possible.

Many thanks

 

[Rick Rothstein]

Hi Rick,

I went with SHG's formula as it presented the data with the colon.

I am not sure why you showed you wanted a dot between your minutes and seconds in your original message then; nor am I sure why you did not correct any of my follow-up messages where I stated to others that you wanted to use a dot.

I would like to display it as '01:08:22' if possible.

I think that will complicate things for any of the previous suggested formulas because of the need to handle things like "2 hrs 17 secs" or "3 hrs 21 mins". Here is the formula I came up with, but you will need to Custom Format the cell using [hh]:mm:ss after you put the formula in the cell...

=TIME(IFERROR(LEFT(A1,FIND("hrs",A1)-1),0),TRIM(RIGHT(SUBSTITUTE(IFERROR(LEFT(A1,FIND("mins",A1)-2),0)," ",REPT(" ",99)),99)),IF(RIGHT(A1,4)="secs",TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A1," ",REPT(" ",100)),200),100)),0))

 

[jamieisrubbishwithexcel]

Hi Rick,

Sorry for confusion caused by myself. Thanks for the final formula which works a treat.