Microsoft KB Archive/93536

{|
 * width="100%"|

Excel: How to Calculate Elapsed Time

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0
 * Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0
 * Microsoft Excel for OS/2, versions 2.2, 3.0

-

SUMMARY
In Microsoft Excel, you can use formulas to calculate the elapsed time for a given period. If the elapsed time is under 24 hours, use direct subtraction. If the elapsed time exceeds 24 hours, use the formula provided in the example in the more information section of this article.

MORE INFORMATION
Use the following formulas to calculate elapsed time for a given interval.

Calculating Elapsed Time for a Period Under 24 Hours
To calculate elapsed time when you know the total will not exceed 24 hours, subtract the start time from the end time as in the following example:

  A1: 1:00 PM      B1: Start time A2: 6:00 PM     B2: End time A3: =A2-A1      B3: Elapsed time

When you format cell A3 using the time format &quot;h:mm&quot; (or &quot;h:mm:ss&quot;) Microsoft Excel will display the hour and minutes (and seconds) of the elapsed time. In this case cell A3 would display &quot;5:00&quot;, representing 5 hours difference between the two times.

Calculating Elapsed Time for a Period that Exceeds 24 Hours
If your end time is more than 24 hours beyond your start time, you need to compute the elapsed hours separately from the elapsed minutes and seconds as in the following example:

  A1: 12/1/92 1:00 PM      B1: Start date and time A2: 12/2/92 6:00 PM     B2: End time A3: =INT((A2-A1)*24)    B3: Elapsed hours A4: =MINUTE(A2-A1)      B4: Elapsed minutes A5: =SECOND(A2-A1)      B5: Elapsed seconds

Note: Format cells A3:A5 using the General number format not a time format.

To display the elapsed time using standard time format (&quot;hh:mm:ss&quot;), concatenate (string together) the values in A3:A5 using the following formula:

  A6: =A3&&quot;:&quot;&A4&&quot;:&quot;&A5    B6: Elapsed time in hh:mm:ss format.

This formula concatenates the hours, minutes, and seconds of elapsed time, separating each with a colon. The ampersand (&) serves as a text joining operator to tie the pieces together.

Microsoft Excel interprets the result of this formula as text and, therefore, uses left alignment for the displayed value. To change the cell alignment to right alignment, choose Alignment from the Format menu and select the Right option under Horizontal in the Alignment dialog box.