Excel: How to Calculate Elapsed Time

 Last reviewed: November 30, 1994
Article 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

KBCategory: kbother
KBSubcategory:

Additional reference 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 3.00 4.0 4.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.