Page 9 of 15PREVNEXT

Make time count by using formulas

Formula calculates hours worked minus lunchtime

Callout 1 Formula in the worksheet
Callout 2 Formula result
Callout 3 Formula in the formula bar

If you need to deduct from your timecard the time spent at lunch each day, the calculation has more steps to it. In cell B6, you would type this formula:

=SUM((B5-B4)*24,(B3-B2)*24)

(B5-B4)*24 subtracts the time at the end of lunch from the quitting time, and then multiplies the result by 24, to convert that result from Excel's decimal time system to a 24-hour system.

(B3-B2)*24 subtracts the starting time from the time at the start of lunch, and then it multiplies that result by 24.

Finally, the SUM function adds up the results of the two separate previous calculations. 8.25 hours is the amount of time worked on Monday, minus the time spent at lunch.

The values B5-B4 and B3-B2 are the arguments, information that tells SUM what to calculate. A comma separates those arguments. The opening parenthesis after SUM and the closing parenthesis at the very end of the formula separate SUM from its arguments. The cell references in the two separate calculations are enclosed in their own sets of parentheses, so that subtraction takes place before multiplication.

Note     SUM is just one of Excel's many, many functions. In "Find functions and enter arguments," you can learn how to find other functions, which you can use for all sorts of calculations.

Page 9 of 15PREVNEXT