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

 

[Gerald Higgins]

If they are all in EXACTLY that format, you can use a formula - no VBA required.

Like this

=time(0,mid(a1,2,2)+0,right(a1,2))

Where a1 contains one of your time text strings.
Apply a time format to the result.

 

[tyija1995]

If its always in the format of "+MM'SS" (M = minute, S = second) then you could manipulate a formula like so:

="00:"&MID(A1,2,2)&":"&MID(A1,5,2)

 

[Ronanm]

Thank you both Gerald and tyija1995. Both perfect.

Thanks

 

[jtakw]

Hi,

Just like to point out, formula suggested in Post # 2 results in Real Time value (Real Number), whereas, formula from Post # 3 results in Text (meaning you won't be able to do further comparison/math).

 

[tyija1995]

Hi,

Just like to point out, formula suggested in Post # 2 results in Real Time value (Real Number), whereas, formula from Post # 3 results in Text (meaning you won't be able to do further comparison/math).

Hey jtakw,

Thanks for the comment - I've just looked at the formula I wrote and it returns a valid number that I can see and thus use for further math.
E.g. if I reference the cell outputted by my formula and run a timevalue function over it I get a valid number answer. Do you have an example where the formula I posted fails to return further mathematical operations?
As far as I can see it will only falter if the cell is set to Text (where the formula is written) - but I have assumed it to be written into a General formatted cell.

 

[jtakw]

My apologies, I stand corrected, if additional arithmetic is applied to the formula cell using your formula, it Will convert to Real Number.

Edit, but as Gerald points out below, the result of your formula alone is still Text...

 

[Gerald Higgins]

When I checked tyija1995's suggestion (using a value in A1 of +08'30) it returned a text string, which is confirmed by applying an ISNUMBER formula to the result, which returns FALSE.
Similarly ISTEXT on the result returns TRUE.

Having said that, if I apply a mathematical formula to the result, such as adding 1/24 to increase the time by another hour, Excel seems to recognise that this text string is actually a time value, and returns 01:08:30.

 

[Gerald Higgins]

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.

 

[Rick Rothstein]

If they are all in EXACTLY that format

For those who might be interested, if there were not always that exact format (leading zeros for single digit minutes and/or seconds), then this formula would work (also works if the format is exact also, of course)...

=0+("00:"&SUBSTITUTE(MID(A1,2,9),"'",":"))

You would just need to format the cell with the time display format of your choosing.