Announcement

Collapse
No announcement yet.

Convert number to days - hours - minutes

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Convert number to days - hours - minutes



    Hello Excel Users

    I would like to convert a number (73.60) to days, hours & minutes to track how much sick or vacation time I have.

    Thanks
    Robert

  • #2
    This is fairly easy to do in Excel. Excel stores dates and time as numbers with each integer equal to one day. So 0.5 is 12 hours, for example. 1.5 would equal 36 hours.

    What units is your 73.60 in? If it's hours, divide it by 24 to convert it to Excel days and set your cell's format to CUSTOM > dd:hh:mm

    If it's in days already, just set the format.

    EDIT: Oops, I should have tested first. This format does not work for greater than 24 hours. Some fancier formulas are, I think, needed.

    [h]:mm will display hours (beyond 24) and minutes, but this is not what you asked.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

    Comment


    • #3
      Adendum. If your 73.60 is in hours in cell A1, then in another cell (I'll use C1), enter:

      =INT(A1/24)

      to get the number of days.

      format another cell to hh:mm and enter:

      =(A1-24*D1)/24

      This will display the hours and minutes.
      Best Regards,
      Tom
      ---------------------------
      Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

      Comment


      • #4
        Something like this might help you!
        Attached Files
        Barbara - aka The Cat Lady

        Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

        Comment


        • #5
          Adendum II:

          Also see Dave's tutorial on working with Dates and Times.

          http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

          And (I forgot to say) Welcome to OzGrid
          Best Regards,
          Tom
          ---------------------------
          Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

          Comment


          • #6
            Cool Tomach but just for clarity, I think you meant:
            Originally posted by thomach
            format D1 to hh:mm and in D1 enter:

            =(A1-24*C1)/24
            Barbara - aka The Cat Lady

            Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

            Comment


            • #7
              Re: Convert number to days - hours - minutes

              That was awesome!

              Thank you very much thomach & Barbarr.

              Comment


              • #8
                Re: Convert number to days - hours - minutes

                May I ask help if the format will be like this 1 d 6 h 15 m . How could I make it appear only in hours and minutes. well it should look like 30h 15 m. Thank you in advance.

                Comment


                • #9


                  Re: Convert number to days - hours - minutes

                  Hi nicedoll.
                  welcome to the forum
                  can you please start your own thread and reference this one if required
                  If the solution helped please donate to RSPCA

                  Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

                  Comment

                  Working...
                  X