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