Time format

[Ricksteruk]

I am currently working on a sheet that i need to automate the following using a formula

At present the atheltes times are in 2 columns, call then B4 and c4
The way the time is entered on the sheet is in B4 it will say 1m and c4 says 14.89s

I need to get this time to display in another cell as 1:14.89 so it combines the time in to a string that excel can then perform time calculations on.

Can anyone help please, for 2 weeks i have been trying to work this out without any results!

[darkyam]

=LEFT(B4,FIND("m",B4)-1)&":"&LEFT(C4,FIND("s",C4)-1)

[Ricksteruk]

Thats awesome, thank you

however i now have another problem with the same thing

Sometimes the time is in a different row, is there a way to search say 20 rows for the time, or even the whole sheet?

[darkyam]

Not sure exactly what you mean. Could you please post your workbook so I can see how the data is laid out?

[Ricksteruk]

I will try and post the sheet later on,


in my first post i gave a example of what cell the time is in, on some of the sheets its in different cells, ie it could be c7 instead of c4 and d7 etc

Is there a way i can make that formula to search say a block of 20 rows for the times?