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

Article translations Article translations
Article ID: 214133 - View products that this article applies to.
This article was previously published under Q214133
Expand all | Collapse all

SUMMARY

This article describes the formula to calculate elapsed time in hours, minutes, and seconds when you are given the velocity and time.

MORE INFORMATION

To calculate elapsed time use the equation:
Time = distance divided by velocity.
Velocity is normally given in terms of distance divided by time, as in miles per hour or feet per second. The one obvious exception to this is knots which are nautical miles per hour. To calculate time, the units must be consistent. For example, if the speed is given in terms of miles per hour (MPH) then the distance must be given in miles.

Excel calculates time as fractional parts of a day, so if the velocity contains an hours unit, you divide the distance by the speed, and then divide the result by 24 (the number of hours in a day). If the velocity contains a seconds unit, you divide the distance by the speed, and then divide the result by 86,400 (the number of seconds in a day).

After the fractional part of a day is calculated, you can format cells to change the data to read as hours, minutes, and seconds.

You can use the steps in the following examples to calculate elapsed time.

Example 1: Miles Per Hour

  1. Start Excel and create the following spreadsheet:
    A1: Distance     B1: MPH      C1: Time
    A2: 50           B2: 65       C2: 
    A3: 100          B3: 55       C3:
    A4: 2000         B4: 75       C4:
    					
  2. In cell C2, enter the following equation:
    =A2/B2/24
    so that the spreadsheet now appears as:
    A1: Distance     B1: MPH      C1: Time
    A2: 50           B2: 65       C2: 0.03  
    A3: 100          B3: 55       C3:
    A4: 2000         B4: 75       C4:
    					
  3. Select cell C2.
  4. On the Format menu, click Cells.
  5. On the Number tab, under Category, select Time.
  6. Under Type, select 37:30:55.
  7. Click OK.

    The spreadsheet now appears as:
    A1: Distance     B1: MPH      C1: Time
    A2: 50           B2: 65       C2: 0:46:09 
    A3: 100          B3: 55       C3:
    A4: 2000         B4: 75       C4:
    					
  8. Select cell C2 and drag to fill to cell C4.

    The spreadsheet now appears as:
    A1: Distance     B1: MPH      C1:  Time
    A2: 50           B2: 65       C2:  0:46:09 
    A3: 100          B3: 55       C3:  1:49:05
    A4: 2000         B4: 75       C4: 26:40:40
    					

Example 2: Feet Per Second

  1. Start Excel and create the following spreadsheet:
    A1: Distance     B1: FPS      C1: Time
    A2: 60           B2: 60       C2: 
    A3: 6000         B3: 70       C3:
    A4: 600000       B4: 80       C4:
    					
  2. In cell C2, enter the following equation:
    =A2/B2/86400
    so that the spreadsheet now appears as:
    A1: Distance     B1: MPH      C1: Time
    A2: 60           B2: 60       C2: 0.00 
    A3: 6000         B3: 70       C3:
    A4: 600000       B4: 80       C4:
    					
  3. Select cell C2.
  4. On the Format menu, click Cells.
  5. On the Number tab, under Category, click Time.
  6. Under Type, select 37:30:55.
  7. Click OK.

    The spreadsheet now appears as:
    A1: Distance     B1: MPH      C1: Time
    A2: 60           B2: 60       C2: 0:00:01 
    A3: 6000         B3: 70       C3:
    A4: 600000       B4: 80       C4:
    					
  8. Select cell C2 and drag to fill to cell C4.

    The spreadsheet now appears as:
    A1: Distance     B1: MPH      C1: Time
    A2: 60           B2: 60       C2: 0:00:01 
    A3: 6000         B3: 70       C3: 0:01:26
    A4: 600000       B4: 80       C4: 2:05:00
    					

Properties

Article ID: 214133 - Last Review: January 24, 2007 - Revision: 2.2
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 2001 for Mac
  • Microsoft Excel 98 for Macintosh
Keywords: 
kbhowto KB214133

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com