Results 1 to 9 of 9

20050124, 11:15 #1
 Join Date
 Mar 2003
 Location
 London, Gtr London
 Posts
 131
 Thanks
 0
 Thanked 0 Times in 0 Posts
Speed/Time/Distance conversions (2003)
I'm not sure if I've got my mathematics 100% correct so please forgive my ignorance if it's not.
I'm trying to chart an altitude vs distance vs elapsed time graph.
For example if I'm travelling at 6 minutes per kiliometre I have worked out that I'm travelling at 10 kilometres per hour. Using the formula Time=Distance/Speed I have calculated the elapsed time in decimal places. When I try and show this figure as actual hours and minutes it starts going wrong. Can anyone help me with this?
I have attached a sample of the data I'm using
Many thanks and kind regards
Hayden

20050124, 11:25 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Speed/Time/Distance conversions (2003)
You are calculating elapsed hours, so the decimal is correct.
if you want to display as elapsed hours/minutes using excel, you must convert it to days and then format it:
Use in C3
<pre>=A3/$D$1/24</pre>
The "24" converts the hours to days. Now format  cells  number(tab) custom:
[h]:mm
to display the "days" in hrs/mins.
Steve

20050124, 11:33 #3
 Join Date
 Mar 2003
 Location
 London, Gtr London
 Posts
 131
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Speed/Time/Distance conversions (2003)
Thanks Steve
That's exactly what I'm wanting.
Hayden

20050307, 10:40 #4
 Join Date
 Mar 2003
 Location
 London, Gtr London
 Posts
 131
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Speed/Time/Distance conversions (2003)
Hello Steve
How do I display a time in hours:mins:secs if it is displayed as 196.53 for example? If I use different columns to calculate the various components of the time and then concatenate the results I get 3:16:53, which I think is right but I was wondering if there was an easier way?
Kind regards
Hayden

20050307, 10:54 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Speed/Time/Distance conversions (2003)
In 196.53, the integer part is a number of minutes, and the decimal part represents the number of seconds. You cannot format this directly as a time.
Say that this number is in A1; in B1, enter the formula
=TIME(0,INT(A1),100*MOD(A1,1))
and format B1 as a time, for example as h:mm:ss or as [h]:mm:ss if you want to allow hours above 24.

20050307, 11:10 #6
 Join Date
 Mar 2003
 Location
 London, Gtr London
 Posts
 131
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Speed/Time/Distance conversions (2003)
Thanks very much Hans

20050307, 11:46 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Speed/Time/Distance conversions (2003)
But the .53 in Hayden's value is not a decimal part, it represents a number of seconds. Hence you cannot simply divide by 1440 (60*24).

20050307, 13:02 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Speed/Time/Distance conversions (2003)
OOPS <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I missed that in his request...
Steve

20050307, 13:06 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Speed/Time/Distance conversions (2003)
<P ID="edit" class=small>(Edited by sdckapr on 07Mar05 08:06. Clarified the post. Thanks, Hans.)</P><font color=red>Note: this scheme assumes that the number is a decimal. If the decimal (0.53) represents 53 seconds and not 0.53 min (31.8 sec) then this scheme is not appropriate...</font color=red>
In addition to Hans' scheme, you can just use:
=a1/24
and format to [h]:mm:ss
Excel dates/times are assumed to be in units of "Days". The 24 converts the hours to days.
Essentially 196 hrs = 196/24 days
If 196 is in minutes then use:
=a1/60/24
to convert from 196 min to days
Steve