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.