Microsoft Office Tutorials and References

In Depth Information

**Calculating Elapsed Time over Days**

Calculating Elapsed Time over Days

Each day has 24 hours. Multiplying 24 by 7 tells us there are 168 hours in a

week. How many hours are in a month? This is not as easy to tell. A month

may have 28, 29, 30, or 31 days.

Counting elapsed time, in hours, could require a complex algorithm. While

there is no single function in Excel for this task, you can combine a couple of

functions in a formula to get the answer. This is another side benefit of the

fact that Excel represents date/time values as serial numbers. This makes it

easy to calculate the number of hours that have passed between two date/

time values.

Figure 13-5 shows a worksheet with start and end date/time values in two

columns. A third column shows the calculated number of elapsed hours for

each start/end pair.

Figure 13-5:

Calculating

elapsed

time.

In Column A and Column B are dates and times. These dates and times are

really just serial numbers with a decimal portion. Using the INT function,

Excel counts the difference in days, even if the span pops over to a new year.

Then the HOUR function is used to calculate the difference of the decimal

portion. The formula for the first row is

=(INT(B3)-INT(A3))*24 + HOUR(B3) - HOUR(A3)

Each successive row has the same formula in Column C but with the cell

references pointed to the values on the row. The first part of the formula

calculates the difference in days and multiplies this by 24 for the total number of

hours in the number of days.