How to Use a Formula to Calculate Time With a Lunch Break in Excelby Jim Campbell
Microsoft Excel is a spreadsheet application that lets you enter formulas into fields. These formulas calculate numbers and display the results in an Excel field. You can use Excel to calculate hours worked with a lunch break subtracted from the value. This is a simple calculation that subtracts a "lunch break" field from the "total hours worked" field. The result is the actual time worked displayed in the designated field.
Type the number of hours worked in one of the Excel fields. For instance, if you have a column of hours worked, a column for lunch break time and then a column for total hours worked, you place the number of hours worked in the first column. In this example, the "A" column is hours worked, "B" is lunch break hours and "C" is the formula field. Type hours worked in field "A1."
Type the number of hours for the lunch break. You can also use fractions of an hour such as a half hour, which is ".5." Type the lunch break time in "B1."
Type the following text into "C1": =a1-b1 In this example, lunch break time is subtracted from the total hours worked. The result is displayed in the C1 field.
Click the C1 field and use the mouse to highlight several fields below it. For instance, highlight C1 through C5 fields. In the "Edit" section at the top of the Excel window, click "Fill Down." This copies the formula to other fields in the column. These fields automatically update to calculate values in adjacent fields. For instance, C2 calculates values in A2 and B2.
Click the "Save" button to store your changes. You can now enter values in the A and B columns and the C column automatically calculates the time worked with a lunch break subtracted.
- photo_camera George Doyle/Valueline/Getty Images