# Converting minutes and seconds as text to a time.

#### Ronanm

##### Board Regular
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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### Gerald Higgins

##### Well-known Member
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.

• Ronanm

#### tyija1995

##### Well-known Member
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

#### Ronanm

##### Board Regular
Thank you both Gerald and tyija1995. Both perfect.

Thanks

#### jtakw

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
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...

Last edited:

#### Gerald Higgins

##### Well-known Member
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

##### Well-known Member
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

##### MrExcel MVP
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.