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:
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.
Search JabSto ::

Custom Search