Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Calculating Time from Speed and Distance?

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


  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Calculating Time from Speed and Distance?

Hi John,

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

Code:
=TEXT(TIME(0,ROUND(C2*60,0),0),"h:mm")
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!
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,202
Default Calculating Time from Speed and Distance?

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default Calculating Time from Speed and Distance?

=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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,202
Default Calculating Time from Speed and Distance?

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I get speed given time (hh:mm:ss) and distance in Excel? Charlie_007 Excel Worksheet Functions 1 September 24th 06 05:02 AM
How to calculate speed given distance and mm:ss time format? rrl Excel Discussion (Misc queries) 1 October 21st 05 05:39 PM
how do I import data (distance and time) from MapPoint to Excel? Reza Excel Discussion (Misc queries) 0 October 20th 05 04:46 AM
time/distance calculation KJO Excel Worksheet Functions 1 April 20th 05 09:13 PM
Time and distance formula drumnotme118 Excel Worksheet Functions 2 November 20th 04 07:22 AM


All times are GMT +1. The time now is 03:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"