Results 1 to 6 of 6

convert H:MM to Minutes?

This is a discussion on convert H:MM to Minutes? within the Excel Questions forums, part of the Question Forums category; How do I convert a H:MM format I.E. 4:30 into minutes? When I use the formula =convert(cell,"hr","mn") it gives me ...

  1. #1
    Board Regular
    Join Date
    Sep 2002
    Posts
    237

    Default convert H:MM to Minutes?

    How do I convert a H:MM format I.E. 4:30 into minutes? When I use the formula =convert(cell,"hr","mn") it gives me 11.25? I don't think it allows the colon? Any ideas how I can convert this? Thank you

  2. #2
    MrExcel MVP Oaktree's Avatar
    Join Date
    Jun 2002
    Location
    Chicago, IL
    Posts
    7,742

    Default

    Excel stores time as fractions of a day, so 4:30 is stored as 0.19 days.

    =CONVERT(A1,"hr","mn") converts 0.19 hrs to minutes, giving you 11.25.

    You'd first have to convert 0.19 days to the correct number of hours.

    Try:

    =CONVERT(A1*24,"hr","mn")

    or just =A1*24*60, formatted as a number.
    "The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell

  3. #3
    Board Regular JazzSP8's Avatar
    Join Date
    Sep 2005
    Location
    Chorley
    Posts
    925

    Default

    Sorry to be a thread intruder, but I would just like to say "Thanks" for the complete randomness of "CONVERT"

    I didnít know that function existed and I think will come in VERY handy on a new project Iím working on that was having me thinking about ways to convert some data, problem solved before it existed I guess!


  4. #4
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,793

    Default

    although be aware that CONVERT is an Analysis ToolPak add-in function.

    For the above I prefer

    =A1*1440

    but you could also use

    =CONVERT(A1,"day","mn")

    or even

    =A1 and format as [m]

    Note: if you use the last of these the underlying cell value will be different to that generated by the first 2

  5. #5
    MrExcel MVP Oaktree's Avatar
    Join Date
    Jun 2002
    Location
    Chicago, IL
    Posts
    7,742

    Default

    Jazz, it's probably a good idea to double check at least a couple of the conversions you're doing. Some versions of Excel have been known to convert some things incorrectly:

    http://support.microsoft.com/kb/283695/en-us
    "The greatest challenge to any thinker is stating the problem in a way that will allow a solution." Bertrand Russell

  6. #6
    Board Regular JazzSP8's Avatar
    Join Date
    Sep 2005
    Location
    Chorley
    Posts
    925

    Default

    And, thanks for the heads up Oaktree - I shall be careful

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