# Thread: converting expression into total mins

1. ## converting expression into total mins

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

2. ## Re: converting expression into total mins

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:
```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

```

3. ## Re: converting expression into total mins

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

4. ## Re: converting expression into total mins

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

didn't work comes with number 0.58333

havent tried the macro not very good at it

5. ## Re: converting expression into total mins

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

6. ## Re: converting expression into total mins

=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

7. ## Re: converting expression into total mins

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

8. ## Re: converting expression into total mins

Originally Posted by Ivn68
=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.

9. ## Re: converting expression into total mins

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

perfect

thanks a lot!!!! : )

10. ## Re: converting expression into total mins

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 "
which indicates 10 hour 12 minutes
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