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

SUMMARY

You may want to calculate the time elapsed for a distance over a given rate 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"

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

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.

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.

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

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.