help with excel time/text formatting

I have cells in the "general" format that contain time data listed (as text) like this:

02m 10s
15m 00s
71m 51s
08m 01s

etc

I need a formula to convert this to a value I can work with - ie, work with as a number or correctly formatted time field so I can run stats off it. I've tried playing with =VALUE(LEFT(A1,2)) etc but get stuck.

Can someone help please?

Ta!
LVL 2
RossAsked:
Who is Participating?
 
jppintoConnect With a Mentor Commented:
You can use a formula like this:

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

Please take a look at the attached file.

jppinto
Book1.xlsx
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Something like this should work:

=TIME(0;LEFT(A1;2);MID(A1;5;2))

For values on A1
0
 
RossAuthor Commented:
Perfect - exactly what I needed, and even included a demo. Very thoughtful, and I'm grateful.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
RossAuthor Commented:
jpaulino - unfortunately your solution didn't work - it gave an error. Thanks for the attempt though!
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
The error you got is because your system uses commas and not semi-comma. If you change to this it will work (I forgot to change it):

=TIME(0,LEFT(A1,2),MID(A1,5,2))

0
 
jppintoCommented:
jpaulino solution works also! The problem is that he posted the formula like this:

=TIME(0;LEFT(A1;2);MID(A1;5;2))

When he should have posted like this:

=TIME(0,LEFT(A1,2),MID(A1,5,2))

This has to do with regional settings where you use , when his Excel uses ;

Take a look at the attached file to see he's formula working.

jppinto


Book1.xlsx
0
 
RossAuthor Commented:
ah perfect! many thanks guys - you have saved me hours of head scratching :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.