Error Checking in Excel
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: converting expression into total mins

  1. #1
    New Member
    Join Date
    Aug 2015
    Posts
    31
    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
    33,098
    Post Thanks / Like
    Mentioned
    58 Post(s)
    Tagged
    20 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,344
    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
    https://www.coursehero.com/file/8261...atabaseDesign/



  4. #4
    New Member
    Join Date
    Aug 2015
    Posts
    31
    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,344
    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
    https://www.coursehero.com/file/8261...atabaseDesign/



  6. #6
    New Member
    Join Date
    Aug 2015
    Posts
    31
    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,344
    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
    https://www.coursehero.com/file/8261...atabaseDesign/



  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,098
    Post Thanks / Like
    Mentioned
    58 Post(s)
    Tagged
    20 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
    31
    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

Some videos you may like

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
  •