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:

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

**52 Bonus tips:**

Thats all for now. Stay awesome until next *time*.

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

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

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

Any ideas why?

@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!

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

good one

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

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.

I had the same issue. You can solve it by changing the format as described above:

Right click cell > Format Cells > (In Number tab) > Custom > Then enter the code [h]:mm

([hh]:mm and [hhh]:mm are nice too if you want to show leading zeros)

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

Sorry, replied to wrong comment....

----

I had the same issue. You can solve it by changing the format as described above:

Right click cell > Format Cells > (In Number tab) > Custom > Then enter the code [h]:mm

([hh]:mm and [hhh]:mm are nice too if you want to show leading zeros)

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.

Thanks very much, extremely useful !