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
 

Some videos you may like

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
Joined
Mar 26, 2007
Messages
9,115
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

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
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)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
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
Joined
Jun 29, 2014
Messages
5,146
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
Joined
Mar 26, 2007
Messages
9,115
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
Joined
Mar 26, 2007
Messages
9,115
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
Joined
Apr 18, 2011
Messages
35,928
Office Version
2010
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,573
Messages
5,469,473
Members
406,655
Latest member
pwilson06

This Week's Hot Topics

Top