Converting minutes and seconds as text to a time.

Ronanm

Board Regular
Joined
Nov 13, 2010
Messages
107
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.
 
Upvote 0
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)
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top