+ Reply to Thread
Results 1 to 16 of 16

Converting times between minutes and fractions and vice versa

  1. #1
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Converting times between minutes and fractions and vice versa

    Hi,

    Anyone able to help with a formula in the missing column.

    Time Formula.xlsx

    Basically have times in fractions of an hour

    1.25 = 1 hr 15 mins
    1.5 = 1 hr 30 mins
    1.75 = 1 hr 45 mins

    and need to be able to convert either or to the other.

    EDIT** The hours and minutes are not in a time format, they are just text so 1 hr 30 is just 1.3.
    Last edited by benoj2005; 10-23-2012 at 07:11 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Converting times between minutes and fractions and vice versa

    use this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    And adopt formatting

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Converting times between minutes and fractions and vice versa

    A TO B is =A2/24 (format as hh:mm)
    B to A is =B2*24 (format as number)

  4. #4
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Re: Converting times between minutes and fractions and vice versa

    cheers chaps. Feel rather silly now, that was so simple!!

  5. #5
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Re: Converting times between minutes and fractions and vice versa

    Actually, there is more to this than I thought.

    The download I do from the clocking system is .15 .30 .45 (minutes). But this is in general format. So to get this to .25 .5 .75 of an hour i can't use the above.
    Last edited by benoj2005; 10-23-2012 at 07:14 AM.

  6. #6
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Converting times between minutes and fractions and vice versa

    If .3 is 30 minutes, how do you get 3 minutes?

  7. #7
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Re: Converting times between minutes and fractions and vice versa

    Quote Originally Posted by zbor View Post
    If .3 is 30 minutes, how do you get 3 minutes?
    .30 sorry.

    The point is, it's text. so to multiply it by 24 does not conver the .30 to .5. Because it's not in a time format to start with.

  8. #8
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Converting times between minutes and fractions and vice versa

    Works for text too.
    What you get as solution?

    If error then there might be some space you need to deal with first.

  9. #9
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Converting times between minutes and fractions and vice versa

    Any arithmetic operation will coerce text to value as Zbor wisely indicated

  10. #10
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Re: Converting times between minutes and fractions and vice versa

    If I do 1.15 * 24 and format the answer as hh:mm it dosent give me what I want, I.e 1.25

    See hereTime Formula.xlsx

  11. #11
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Converting times between minutes and fractions and vice versa

    Because you must divide it:

    =C2/24

    (also, no need for + sign in front of equations)

  12. #12
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Re: Converting times between minutes and fractions and vice versa

    just a habit, + is easier to hit than =, it then adds an = and a + after you hit enter

    So, 1.15/24 = 1:09 (formatted as hh:mm)

    I want this to show 1.25 (Fraction of an hour)

    Time Formula.xlsx

  13. #13
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Converting times between minutes and fractions and vice versa

    Here, try this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Re: Converting times between minutes and fractions and vice versa

    Quote Originally Posted by zbor View Post
    Here, try this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    That's the one Thank you!

  15. #15
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Converting times between minutes and fractions and vice versa

    To convert 1.15 to 1.25 etc. try

    =DOLLARDE(C2,60)
    Audere est facere

  16. #16
    Forum Contributor
    Join Date
    02-14-2012
    Location
    Warrington
    MS-Off Ver
    Excel 365
    Posts
    376

    Re: Converting times between minutes and fractions and vice versa

    Oh even easier to remember! Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1