converting expression into total mins
Power Query Course in Spanish
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: converting expression into total mins

  1. #1
    New Member
    Join Date
    Aug 2015
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    31,838
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    11 Thread(s)

    Default 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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Alan Sidman
    Win 10--Office 2016

    Click below for a white paper on Data Base Design
    http://r937.com/relational.html



  4. #4
    New Member
    Join Date
    Aug 2015
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Alan Sidman
    Win 10--Office 2016

    Click below for a white paper on Data Base Design
    http://r937.com/relational.html



  6. #6
    New Member
    Join Date
    Aug 2015
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by Ivn68; Apr 15th, 2018 at 11:26 PM.

  7. #7
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Last edited by alansidman; Apr 15th, 2018 at 11:27 PM.
    Alan Sidman
    Win 10--Office 2016

    Click below for a white paper on Data Base Design
    http://r937.com/relational.html



  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    31,838
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    11 Thread(s)

    Default Re: converting expression into total mins

    Quote Originally Posted by Ivn68 View Post
    =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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    New Member
    Join Date
    Aug 2015
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: converting expression into total mins

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


    perfect

    thanks a lot!!!! : )

  10. #10
    New Member
    Join Date
    Apr 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com