Excel: How to Calculate Elapsed Time

ID: Q93536

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 "h:mm" (or "h:mm:ss") Microsoft Excel will display the hour and minutes (and seconds) of the elapsed time. In this case cell A3 would display "5:00", 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 ("hh:mm:ss"), concatenate (string together) the values in A3:A5 using the following formula:


   A6: =A3&":"&A4&":"&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.

REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, pages 108-110
"Microsoft Excel User's Guide 2," version 4.0, page 237
"Microsoft Excel Function Reference," version 4.0, pages 244, 275 and 378

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.1c 2.10c 2.1d 2.10d 2.2 2.20 2.21 3.0

Keywords :
Version :
Platform :
Issue type :