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

 

[mrssimpson]

IVN68 I have the same question however
i have data that is imported into excel.
the data is expressed like this "[TABLE="class: cms_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
Please assist

 

[Rick Rothstein]

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

Give this formula a try...

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

 

[mrssimpson]

Give this formula a try...

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

I tried that and for 15 h 10 m it gave [TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl68, width: 84, align: right"]0.6319444[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

I tried that and for 15 h 10 m it gave [TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl68, width: 84, align: right"]0.6319444[/TD]
[/TR]
</tbody>[/TABLE]

That is how Excel sees time values... select the cell with the formula(s) and then set the cell format to the time display that you want to see.

 

[mrssimpson]

thank you I changed my format to 15 h, 10 m and used a replace h with h, and used your formula
=0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"h",":"),"m",":00") and let that work. Thanks for your help.

 

[Ivn68]

I can't get the vba macro to work
Column a is first name column b is last name column c is the time
The format is " 1 h , 15 m". And I want it converted into total minutes. So it would come back 75.

 

[Ivn68]

Help!

 

[Rick Rothstein]

I can't get the vba macro to work
Column a is first name column b is last name column c is the time
The format is " 1 h , 15 m". And I want it converted into total minutes. So it would come back 75.

You "time values" have extra spaces that the original poster's values did not have and they are getting in the way of the macro (and formulas) work. Here is a revised macro that should work for the data structure you posted...

[table="width: 500"]
[tr]
	[td]Sub ConvertToTimeValue()
  With Range("C1", Cells(Rows.Count, "C").End(xlUp))
    .Value = Evaluate("IF({1},0+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" & .Address & ","" "",""""),""h,"","":""),""m"","":00""))")
    .NumberFormat = "[h]:mm"
  End With
End Sub[/td]
[/tr]
[/table]

 

[Ivn68]

The time is expressed like "1 h, 15 m". It converts it to " 1:15". I need it to convert it to total minutes so it should be "75"

 

[Rick Rothstein]

The time is expressed like "1 h, 15 m". It converts it to " 1:15". I need it to convert it to total minutes so it should be "75"

Sorry, I missed that part of your request. Give this macro a try instead...

[table="width: 500"]
[tr]
	[td]Sub ConvertToTotalMinutes()
  With Range("C1", Cells(Rows.Count, "C").End(xlUp))
    .Value = Evaluate("IF({1},1440*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" & .Address & ","" "",""""),""h,"","":""),""m"","":00""))")
    .NumberFormat = "General"
  End With
End Sub[/td]
[/tr]
[/table]