Microsoft Office Tutorials and References
In Depth Information
Time-Related Functions
As you can see, the formula returns a seemingly incorrect total (17 hours, 45 minutes). The total
should read 41 hours, 45 minutes. The problem is that the formula is displaying the total as a
date/time serial number of 1.7395833, but the cell formatting is not displaying the date part of
the date/time. The answer is incorrect because cell B9 has the wrong number format.
To view a time that exceeds 24 hours, you need to change the number format for the cell so
square brackets surround the hour part of the format string. Applying the number format here to
cell B9 displays the sum correctly:
[h]:mm
Figure 6-7 shows another example of a worksheet that manipulates times. This worksheet keeps
track of hours worked during a week (regular hours and overtime hours).
Figure 6-7: An employee time sheet workbook.
The week’s starting date appears in cell D5, and the formulas in column B fill in the dates for the
days of the week. Times appear in the range D8:G14, and formulas in column H calculate the
number of hours worked each day. For example, the formula in cell H8 is
=IF(E8<D8,E8+1-D8,E8-D8)+IF(G8<F8,G8+1-G8,G8-F8)
The first part of this formula subtracts the time in column D from the time in column E to get the
total hours worked before lunch. The second part subtracts the time in column F from the time in
column G to get the total hours worked after lunch. I use IF functions to accommodate graveyard
shift cases that span midnight — for example, an employee may start work at 10:00 PM and begin
lunch at 2:00 AM. Without the IF function, the formula returns a negative result.

Search JabSto ::

Custom Search