XL: Formula for Distance/Velocity Returned in Hours and Minutes

ID: Q68253

The information in this article applies to:

Microsoft Excel for the Macintosh, versions 2.0, 3.0, 4.0, 5.0
Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for OS/2, versions 2.2, 2.21, 3.0
Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
Microsoft Excel 98 Macintosh Edition

SUMMARY

You may want to calculate the time elapsed for a distance over a given rrat of speed equation and return the result in the "hours:minutes" format.

This article contains examples of formulas that return the value in the "hours:minutes" format. To do use the examples, first type following sample data into the corresponding cells in a worksheet:

A1: Distance B1: MPH C1: Time

A2: 50 B2: 65 C2: "See below"

Then, use one of the following methods.

Method 1: Time Does Not Exceed 24 Hours

Use this method if you do not expect the time to exceed 24 hours.

In cell C2 enter the following formula:

=A2/B2/24

This formula returns the result in the serial number format. Change this result to a time format by using one of the following methods:

Microsoft Excel 7.0 and later:

To change the format, follow these steps:
1. On the Format menu, click Cells.
2. Click the Number tab.
3. In the Category list, click Time. In the Type list, click 13:30. Click OK.

Microsoft Excel 5.0:

To change the format, follow these steps:
1. On the Format menu, click Cells.
2. Click the Number tab.
3. In the Category list, click Time. In the Format Codes list, click h:mm. Click OK.

Microsoft Excel 4.0 and earlier:

To change the format, follow these steps:
1. On the Format menu, click Number.
2. In the Format Number list, click h:mm.

Method 2: Time Does Not Exceed 24 Hours

Use this method if you expect the time to exceed 24 hours.

NOTE: The result of this formula is actually a text string; it is not a number.

To enter the formula, type the following sample formula in cell C2: =TEXT(INT(A2/B2),"00")&":"&TEXT(((A2/B2)-INT(A2/B2))*60,"00")
When cell A2 contains the value of the distance covered, and cell B2 contains the value of the rate of speed, this formula returns the result in the "hours:minutes" format.

NOTE: The TEXT function in the formula converts both the hours and the minutes to text, and then concatenates them together with a colon that separates the two text strings.

Additional query words: 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 Distance Velocity 7.0 xl97

Keywords : kbdta xlformula kbhowto
Version : WINDOWS:2.0,3.0,4.0,5.0,7.0,97;MACINTOSH:2.0,3.0,4.0,5.0
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbhowto