Converting minutes and seconds as text to a time.

[Ronanm]

Hi

I have time in an Excel sheet showing minutes and seconds as text e.g. +08'30 and I'd like them to be shown (using VBA as there are thousands of rows) as 00:08:30 which is a number format. Any ideas?

Thanks

 

[tyija1995]

And having said all that, while tyija1995's proposal can be used for further calculations, I still think my solution is better.

One reason is that with my solution the format can easily be changed from hh:mm:ss to hh:mm or a decimal figure, or any other Excel numeric format.

Good observation Gerald! I agree with you that yours is better, due to the fact like you say mine returns a TRUE boolean for ISTEXT but FALSE for ISNUMBER.
I suppose if I wrapped my formula in a VALUE function then that would return ISNUMBER = TRUE, then apply formatting from that!

EDIT: Or I could have just put parenthesis over my formula and tagged a +0 at the end, as this will change the value to numerical