Excel: Calculating Elapsed Time

Posted August 28, 2006 by Rob Rogers in Microsoft Excel

To create a formula that calculates the amount of time elapsed between two different times of the day, subtract the starting time from the ending time. This tech-recipe describes how to accomplish this.


Calculating the time that has elapsed between a starting and ending time requires a simple formula. For example, you can enter a person’s clock-in time in cell B12 and his or her clock-out time in C12. In cell D12, you would enter the following subtraction formula:

=C12-B12

Excel then returns the difference in cell D12 as a decimal value representing what fraction that difference represents of an entire day (that is, a 24-hour period). If, for example, cell B12 contains the starting time of 9:00 am and cell C12 contains an ending time of 5:00 pm, Excel returns the following decimal value in cell D12:

8:00 AM

To convert this time of day into its equivalent decimal number, you convert the time format automatically given to it by the General format Ctrl+Shift+~ or by right-clicking on the cell and selecting Format Cells and then selecting the General format, which displays the following result in cell D14:

0.333333333

To convert this decimal number (representing the fraction of an entire day) into the number of hours that have elapsed, simply multiply this result by 24 as in =D12*24, which gives you a result of 8 hours or modify your formula to =(C12-B12)*24.

 

About Rob Rogers

Once a prolific author here on Tech-Recipes, Rob has moved on to greener pastures.
View more articles by Rob Rogers

The Conversation

Follow the reactions below and share your own thoughts.