Convert fractional Excel time to hours & minutes [Quick tip]

Posted on August 19th, 2014 in Excel Howtos - 9 comments

Time for another quick Excel tip.

Convert fraction to hours & minutes in Excel - Tip

Lets say the park near your house rents tennis courts by hour. And they charge $10 per hour. At the end of an intense tennis playing week, Linda, the tennis court manager called you up and said you need to pay $78 as rent for that week.

How many hours did you play?

Of course 78/10 = 7.8 hours.

But we all know that 7.8 hours makes no sense.

We also know that 7.8 hours is really 7 hours 48 minutes.

So how to convert 7.8 hrs to 7:48 ?

That is our quick tip for the day.

Custom cell formatting to show hours & minutes in Excel from fractional timeSimple, assuming the fractional time is in cell A1,

Use below steps to convert it to hours & minutes:

  1. In the target cell, write =A1/24
  2. Select the target cell and press CTRL+1 to format it (you can also right click and select format cells)
  3. Select Custom from “Number” tab and enter the code [h]:mm
  4. Done!

52 Bonus tips:

Thats all for now. Stay awesome until next time.

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

9 Responses to “Convert fractional Excel time to hours & minutes [Quick tip]”

  1. Debraj Roy says:

    Hi Purna..

    Again a great tip.. Its a great way to convert Fractional Time..
    By the way.. Excel has two great and rarely used formula..

    =DOLLARFR(7.8,60) and =DOLLARDE(7.48,60)

    basically US Account person uses those to convert some currency denomination.. and we can use it to convert Year(i.e 3.11 Year = 3 year 11 month) and Week(6.5 week = 6 week 5 days), in the same manner...

  2. Jason says:

    This doesn't work for me. When applying the custom format of [h]:mm to 7.8 I get 187:12

    Any ideas why?

    • Hui... says:

      @Jason
      7.8 in Excel talk means 7.8 days
      =7.8*24
      =187.2 Hrs
      =187 Hrs 12 Mins

      If you follow Chandoo's instructions you will see that he divides the 7.8 by 24 to get it to a fraction of a day

      Simple, assuming the fractional time is in cell A1,

      Use below steps to convert it to hours & minutes:

      1. In the target cell, write =A1/24
      2. Select the target cell and press CTRL+1 to format it (you can also right click and select format cells)
      3. Select Custom from “Number” tab and enter the code [h]:mm
      4. Done!

  3. WhoKnows says:

    Hi, sorry to point this out but Column C Header is misspelt 'Hours Palyed'

  4. abhishek malik says:

    good one

  5. Julia says:

    So how do I go the other way and get hours and minutes to fractional time?

    • Chandoo says:

      If you have 7.5 in cell A1,

      - Use int(A1) to get the hours.
      - Use mod(A1,1)*60 to get minutes.

      If you have 7:30 (formatted as time) in A1

      - Use hours(a1) to get hours
      - Use minutes(a1) to get minutes.

  6. Srikanth says:

    It doesnt work for greater than 24 hours
    It returns 1:30 for 25.5 hours. It should have returned 25:30

    Ideally I would right function as
    =QUOTIENT(A1,1)&":"&MOD(A1,1)*60

  7. Daniel says:

    Clever use of MOD here to extract the decimal part of a number. Divide a number containing a decimal by 1 and return the remainder. Humm. Very clever.

Leave a Reply