Announcement

Collapse
No announcement yet.

Convert Number To Hours & Minutes

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Convert Number To Hours & Minutes

    How can I convert an ordinary number i.e. 3.45 to Hours & Minutes?

    A colleague has columns of data that when summed are all wrong i.e. 8.65 instead of 9 Hours 5 minutes.

  • #2
    Re: Convert Number To Hours & Minutes

    Not much to go on, Timbo. Are we talking 3.45 decimal, 3.45 hours, or 3.45 days? See Excel Dates & Times.
    .

    Comment


    • #3
      Re: Convert Number To Hours & Minutes

      Formatted as a number to two decimal places.

      Comment


      • #4
        Re: Convert Number To Hours & Minutes

        Sample workbook please, then...
        .

        Comment


        • #5
          Re: Convert Number To Hours & Minutes

          See attached.
          Attached Files

          Comment


          • #6
            Re: Convert Number To Hours & Minutes

            OK, Assuming these are hours, so 3.45 needs to show as 3:27, you need to divide by 24, and format as Custom [h]:mm
            .

            Comment


            • #7
              Re: Convert Number To Hours & Minutes

              Why 3.27? 3.45 represents 3 hours 45 minutes.

              In the sample workbook that I attached and said earlier 8.65 should equal 9 hours 5 minutes.

              Comment


              • #8
                Re: Convert Number To Hours & Minutes

                why not enter the times in the right format to begin with and write 3:45? this way xl will automatically assume it's a time. 8:65 however will not be recognized. That really does not make sense.
                In formulae, depending on your locale, you might have to replace ; with , or vice versa.

                Comment


                • #9
                  Re: Convert Number To Hours & Minutes

                  Originally posted by Timbo
                  A colleague has columns of data that when summed are all wrong i.e. 8.65 instead of 9 Hours 5 minutes.
                  hi Timbo,

                  what you should do is correct the mistake yr colleague did before summing them up

                  try this formula to convert them to time
                  then you should be able to get the correct sum:

                  =SUBSTITUTE(A1,".",":")

                  where A1 is the wrong entry.

                  hth
                  Cheers
                  ___________
                  Xlite
                  All you need to learn VBA is an internet connection and Ozgrid.com

                  Comment


                  • #10
                    Re: Convert Number To Hours & Minutes

                    Try this, applied to A12:

                    =(INT(A12)*60+MOD(A12,1)*100)/60/24

                    and format as Custom [h]:mm
                    .

                    Comment


                    • #11
                      Re: Convert Number To Hours & Minutes

                      Get people to enter it as 3:45 instead of 3.45. That way you should be able to add and subtract as hours and minutes.

                      Obviously this isn't a great 'solution', as you can't really apply it to data already entered, but advice on how to help for the future. Sorry I can't be any more helpful.

                      Comment


                      • #12
                        Re: Convert Number To Hours & Minutes

                        Everyone: Thanks for all the advice I'll kick it back and get him to do some re-formatting.

                        Comment


                        • #13
                          Re: Convert Number To Hours & Minutes

                          Originally posted by Timbo
                          Everyone: Thanks for all the advice I'll kick it back and get him to do some re-formatting.
                          ok, but doesn't my formula work?
                          Cheers
                          ___________
                          Xlite
                          All you need to learn VBA is an internet connection and Ozgrid.com

                          Comment


                          • #14
                            Re: Convert Number To Hours & Minutes

                            Originally posted by ByTheCringe2 View Post
                            Try this, applied to A12:

                            =(INT(A12)*60+MOD(A12,1)*100)/60/24

                            and format as Custom [h]:mm

                            This is very good formula for converting Numbers to time i really like this formula it is my favorite formula.

                            Comment

                            Working...
                            X