converting expression into total mins

Ivn68

Board Regular
Joined
Aug 21, 2015
Messages
70
i have data that is imported into excell.
the data is expressed like this"1h, 24m"
which indicates 1 hour 24 minutes
how can i convert that into total mins
excell doesnt recognise "1h, 24m" as a measure of time
is there any vba formula i could use to convert it into total mins?

thanks for any help
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Assuming your data format is exactly as shown (h followed by a comma and a space for the hours, an m for the minutes)...

If you can make use of a formula...

=0+SUBSTITUTE(SUBSTITUTE(A1,"h, ",":"),"m",":00")

otherwise, if you want a macro to do the conversion within the cell containing that text...
Code:
[table="width: 500"]
[tr]
	[td]Sub ConvertToTimeValue()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate("IF({1},0+SUBSTITUTE(SUBSTITUTE(" & .Address & ",""h, "","":""),""m"","":00""))")
    .NumberFormat = "[h]:mm"
  End With
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Assume your data is in Cell A4, then in B4 apply this formula and copy down

=TIME(LEFT(A4,FIND("h",A4)-1),MID(A4,FIND(",",A4)+2,2),0)

and format as Custom hh:mm
 
Upvote 0
=0+SUBSTITUTE(SUBSTITUTE(A1,"h, ",":"),"m",":00")

didn't work comes with number 0.58333

havent tried the macro not very good at it
 
Upvote 0
To run Rick's code:

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button
 
Upvote 0
=TIME(LEFT(A4,FIND("h",A4)-1),MID(A4,FIND(",",A4)+2,2),0)

it comes back 1:24AM

need it to come back 84

also need "35h, 15m" to convert into 2115
 
Last edited:
Upvote 0
change my formula to =LEFT(A4,FIND("h",A4)-1)*60 +MID(A4,FIND(",",A4)+2,2) and format it as a number
 
Last edited:
Upvote 0
=0+SUBSTITUTE(SUBSTITUTE(A1,"h, ",":"),"m",":00")

didn't work comes with number 0.58333
That value is the time serial number... select the cell and format it with the time display you want.
 
Upvote 0
=LEFT(A4,FIND("h",A4)-1)*60 +MID(A4,FIND(",",A4)+2,2)


perfect

thanks a lot!!!! : )
 
Upvote 0
Rick I have the same question however
i have data that is imported into excel.
the data is expressed like this "
10 h 12 m "

<tbody>
</tbody>
which indicates 10 hour 12 <vb_highlight>minutes</vb_highlight>
how can i convert that into total mins
excell doesnt recognize "10 h 12 m as a measure of time
is there any vba formula i could use to convert it into total mins?

I can assume
=0+SUBSTITUTE(SUBSTITUTE(A1,"h, ":"),"m":00")
But I don't want to assume
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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