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.
