converting expression into total mins

[Ivn68]

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

 

[Rick Rothstein]

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...

[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]

 

[alansidman]

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

 

[Ivn68]

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

didn't work comes with number 0.58333

havent tried the macro not very good at it

 

[alansidman]

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

 

[Ivn68]

=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

 

[alansidman]

change my formula to =LEFT(A4,FIND("h",A4)-1)*60 +MID(A4,FIND(",",A4)+2,2) and format it as a number

 

[Rick Rothstein]

=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.

 

[Ivn68]

=LEFT(A4,FIND("h",A4)-1)*60 +MID(A4,FIND(",",A4)+2,2)


perfect

thanks a lot!!!! : )

 

[mrssimpson]

Rick I have the same question however
i have data that is imported into excel.
the data is expressed like this " [TABLE="width: 101"]
<tbody>[TR]
[TD="width: 101"]10 h 12 m "[/TD]
[/TR]
</tbody>[/TABLE]
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