Microsoft KB Archive/68253

{|
 * width="100%"|

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

 * }

-

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, 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 &quot;hours:minutes&quot; format.

This article contains examples of formulas that return the value in the &quot;hours:minutes&quot; 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: &quot;See below&quot;'''

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:


 * On the Format menu, click Cells.
 * Click the Number tab.
 * 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:


 * On the Format menu, click Cells.
 * Click the Number tab.
 * 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:


 * On the Format menu, click Number.
 * 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),&quot;00&quot;)&&quot;:&quot;&TEXT(((A2/B2)-INT(A2/B2))*60,&quot;00&quot;) 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 &quot;hours:minutes&quot; 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

Technology :