Solved Converting hr min format to a number representing minutes

February 18, 2013 at 10:15:22
Specs: Windows 7
I have a hr and minute format given ie 1hr 27min in a cell. I need to convert it to either minutes or hours as a number only. Any help would be appreciated

See More: Converting hr min format to a number representing minutes

Report •

#1
February 18, 2013 at 12:03:30
It is formatted as text (1 hr 27min). There will never be days -- it will always be just hr min.

Report •

#2
February 18, 2013 at 12:56:19
✔ Best Answer
You have posted 2 different formats for the time.

In your OP you used: 1hr 27min

In Response # 2 you used: 1 hr 27min

Note the extra space before hr in Response # 2.

Since we are going to have to parse the numbers out of the text string, it would help if we knew if the layout will be consistent, e.g. always a space after the numbers, never a space after the numbers, etc.

If the spaces will be there sometimes and other times not, then the difficulty level increases substantially.

If there will always be a space after the numbers, then this should work:

=MID(A3,1,FIND(" ",A3))+MID(A3,FIND(" ",A3,FIND(" ",A3)+1)+1,2)/60

If there will never be a space after the numbers, then this should work:

=MID(A3,1,FIND("h",A3)-1)+MID(A3,FIND(" ",A3)+1,FIND("m",A3)-FIND(" ",A3)-1)/60

Both formulas will return 1.45, depending on the presence of the spaces.

They should work for all values of hours (up to 13 digits) and both single and double digit minutes.

You can see how much difference the presence and location of the spaces make.

Excel was never really built to be a "text editor" so the the Text based functions can be quite cumbersome.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
February 19, 2013 at 09:46:36
There is never a space after the number. Your solution worked perfectly. Thanks!

Report •
Related Solutions


Ask Question