Microsoft Office Tutorials and References
In Depth Information
Summing times that exceed 24 hours
To view a time that exceeds 24 hours, you need to apply a custom number format for the
cell so that 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 16.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 16.7
An employee timesheet 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 ﬁ rst 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
Search JabSto ::

Custom Search