Microsoft Office Tutorials and References

In Depth Information

**Time-Related Functions**

The following formula in cell H17 calculates the weekly total by summing the daily totals in

column H:

=SUM(H8:H14)

This worksheet assumes that hours that exceed 40 hours in a week are considered overtime

hours. The worksheet contains a cell named
Overtime
(cell C23) that contains 40:00. If your

standard workweek consists of something other than 40 hours, you can change the
Overtime
cell.

The following formula (in cell E18) calculates regular (non-overtime) hours. This formula returns

the smaller of two values: the total hours, or the overtime hours.

=MIN(E17,Overtime)

The final formula, in cell E19, simply subtracts the regular hours from the total hours to yield the

overtime hours:

=E17-E18

The times in H17:H19 may display time values that exceed 24 hours, so these cells use a custom

number format:

[h]:mm

The workbook shown in Figure 6-7,
time sheet.xlsm
, also appears on the companion

CD-ROM.

Calculating the difference between two times

Because times are represented as serial numbers, you can subtract the earlier time from the later

time to get the difference. For example, if cell A2 contains 5:30:00 and cell B2 contains 14:00:00,

the following formula returns 08:30:00 (a difference of eight hours and 30 minutes):

=B2-A2

If the subtraction results in a negative value, however, it becomes an invalid time; Excel displays

a series of hash marks (#######) because a time without a date has a date serial number of 0. A

negative time results in a negative serial number, which cannot be displayed — although you can

still use the calculated value in other formulas.