# 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.

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.

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!

52 Bonus tips:

Thats all for now. Stay awesome until next time.

 What is the average speed of this road trip? [Solution & Discussion] CP018: Dont be a Pivot Table Virgin!
 Written by Chandoo Tags: custom cell formatting, date and time, Learn Excel, Microsoft Excel Formulas, quick tip 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.

 What is the average speed of this road trip? [Solution & Discussion] CP018: Dont be a Pivot Table Virgin!