Excel: Calculating Elapsed Time

Contributed by Rob Rogers  
Tagged: 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.

For example, you enter a person’s clock-in time in cell B12 and 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