I am trying to calculate the time expressed in hours and minutes based
on the average speed and distance travelled, but I'm having trouble
getting it to display correctly as e.g. 00:16 for 16 minutes.

For example I might have 16.8 as the miles travelled in cell A2 and 64
as the average Mph in cell B2. In cell C2 I have =A2/B2 which gives
0.2625. So I am trying to convert that decimal to h:mm. It should come
out something like 00:16. If anyone has any ideas on that I'd
appreciate it.

Thanks for your help

John

I am trying to calculate the time expressed in hours and minutes based
on the average speed and distance travelled, but I'm having trouble
getting it to display correctly as e.g. 00:16 for 16 minutes.

For example I might have 16.8 as the miles travelled in cell A2 and 64
as the average Mph in cell B2. In cell C2 I have =A2/B2 which gives
0.2625. So I am trying to convert that decimal to h:mm. It should come
out something like 00:16. If anyone has any ideas on that I'd
appreciate it.

Since 0.2625 is the fraction of an hour, multiply it by 60 to get the number
of minutes... 15.75 where the .75 is fraction of a minute (so you apparently
will want to round this number to get your 16 answer).

Rick

I am trying to calculate the time expressed in hours and minutes based
on the average speed and distance travelled, but I'm having trouble
getting it to display correctly as e.g. 00:16 for 16 minutes.

For example I might have 16.8 as the miles travelled in cell A2 and 64
as the average Mph in cell B2. In cell C2 I have =A2/B2 which gives
0.2625. So I am trying to convert that decimal to h:mm. It should come
out something like 00:16. If anyone has any ideas on that I'd
appreciate it.

Since 0.2625 is the fraction of an hour, multiply it by 60 to get the
number of minutes... 15.75 where the .75 is fraction of a minute (so you
apparently will want to round this number to get your 16 answer).

Use Teethless mama's solution... don't know why, but I thought I was in the
"programming" newsgroup when I answered this.

Rick

=0.2625/24

Format cell as h:mm


"John" wrote:

I am trying to calculate the time expressed in hours and minutes based
on the average speed and distance travelled, but I'm having trouble
getting it to display correctly as e.g. 00:16 for 16 minutes.

For example I might have 16.8 as the miles travelled in cell A2 and 64
as the average Mph in cell B2. In cell C2 I have =A2/B2 which gives
0.2625. So I am trying to convert that decimal to h:mm. It should come
out something like 00:16. If anyone has any ideas on that I'd
appreciate it.

Thanks for your help

John

Hi John,

To convert the decimal value of 0.2625 to hours and minutes, you can use the following formula:

Here's how it works:

1. ROUND(C2*60,0) multiplies the decimal value by 60 to convert it to minutes, and then rounds it to the nearest whole number.
2. TIME(0,ROUND(C2*60,0),0) creates a time value using the rounded minutes.
3. TEXT(TIME(0,ROUND(C2*60,0),0),"h:mm") formats the time value as "h:mm" (hours and minutes).

So in your example, if you enter the formula in cell D2, it should display 00:16.

I hope that helps!