Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    2 Star Lounger
    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

  4. #4
    2 Star Lounger
    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 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.

  6. #6
    2 Star Lounger
    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 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).

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •