Speed/Time/Distance conversions (2003)

[HAYDENNOTHNAGEL]

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

[sdckapr]

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

[HAYDENNOTHNAGEL]

Thanks Steve

That's exactly what I'm wanting.

Hayden

[HAYDENNOTHNAGEL]

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

[HansV]

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.

[HAYDENNOTHNAGEL]

Thanks very much Hans

[HansV]

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

[sdckapr]

OOPS <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I missed that in his request...

Steve

[sdckapr]

<P ID="edit" class=small>(Edited by sdckapr on 07-Mar-05 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