Many people are surprised to discover that when you sum a series of times that exceed 24 hours, Excel doesn’t display the correct total. Figure 16-7 shows an example. The range B2:B8 contains times that represent the hours and minutes worked each day. The formula in cell B9 is:

=SUM(B2:B8)

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.

FIGURE 16-7
Incorrect cell formatting makes the total appear incorrectly.

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-8 shows another example of a worksheet that manipulates times. This worksheet keeps track of hours worked during a week (regular hours and overtime hours).

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)

FIGURE 16-8
An employee timesheet workbook.

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.

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 in excess of 40 hours in a week are considered overtime hours. The worksheet contains a cell named Overtime, in cell C23. This cell contains 40:00. If your standard workweek consists of something other than 40 hours, you can change this formula.

The following formula (in cell H18) calculates regular (nonovertime) hours. This formula returns the smaller of two values: the total hours or the overtime hours:

=MIN(E17,Overtime)

The final formula, in cell H19, 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

• Mountain High Ski Resort, Wrightwood, CA Mountain High Ski Resort is located in: Wrightwood, CA Phone: (888) 754-7878 About the Resort: This is actually three resorts...
• Coastal California (Regional Guide) User Reviews Send this to a friend Coastal California (Regional Guide) Manufacturer: Customer Rating: List Price: Varies based on product...
• Redemption for Hilton’s Best Rate Guarantee A few weeks ago I asked if Hilton Hotels Best Rate Guarantee is legit? Well I am happy to report...
• Joint Banking, Budgets, and Savings Joint budgeting has been a topic for the past few weeks on some personal finances sites. Several bloggers have been...
• Minimum Wage Goes Up Today - Too Bad Everything Costs More. Starting today, the Federal minimum wage will go up $.70 an hour, or for a 40 hour work week, a...