Share via

Calculate speed in kilometres per hour from distance and time.

Anonymous
2017-12-14T18:04:36+00:00

Hello.

I am using excel to create a record of some cycling events. I have Distance in cell F1 (formatted using Custom) -> 0.00" km" so if i type "100" into the cell, it displays as "100 km".  I have Time in cell J2 (formatted using Custom) -> hh:mm:ss:   and i type in the cell "=TIME(01,02,03) so it will display time as "01:02:03: but this omits fractions of a second, which can occur, in some events. I have Speed in cell K2 - but whilst I have tried various formula, I cannot calculate this correctly.

Can anyone suggest a solution?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

11 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. DaveM121 871.8K Reputation points Independent Advisor
    2017-12-14T18:32:50+00:00

    Hi DVE88, the formula you need is:

    =(LEFT(F1,5))/J2/24

    2 people found this answer helpful.
    0 comments
  2. DaveM121 871.8K Reputation points Independent Advisor
    2017-12-14T20:29:26+00:00

    DVE88, I just formatted that cell as Custom - hh:mm:ss

    I don't have AM or PM showing . . .

    I only get AM or PM when I format the cell as Custom - h:mm:ss am/pm

    1 person found this answer helpful.
    0 comments
  3. DaveM121 871.8K Reputation points Independent Advisor
    2017-12-14T19:15:22+00:00

    Actually I think it is your Time formula that is the issue . . .

    I am using 02:24:00, do you need the milliseconds?

    0 comments
  4. DaveM121 871.8K Reputation points Independent Advisor
    2017-12-14T19:13:08+00:00

    Hi DVE88, grab this sample sheet from DropBox, that formula works on that sheet!

    https://www.dropbox.com/s/mbete4361toqng5/Speed...

    0 comments
  5. Anonymous
    2017-12-14T18:47:12+00:00

    Hey Dave, thank you for the fast response. I have just tried this formula (directly into K2) but it returned an error.

    Here is the data i used.

    F2 cell data is "100"  it is formatted under Custom 0.00" km" it displays as "100 km"

    J2 cell data is "2hr 24min" it is formatted under Custom  [hh]:mm:ss.00 using cell input

                           formula =TIME(2,24,0) displays as "02:24:00:00"

    K2 cell data is general and used the formula you added as cell input.

    0 comments