Microsoft Excel contains over 300 built-in formulas and functions to create a productive worksheet. The formulas and functions provide a number of roles, including calculating values, analysing data and performing logical tests. Individuals or small businesses can use Microsoft Excel to calculate regular hours, overtime hours and payroll. Using a few formulas and the IF function, you can create an Excel spreadsheet that calculates the time and overtime of a single employee or an entire workforce.

- Skill level:
- Moderate

### Other People Are Reading

## Instructions

- 1
Open Excel and click cell "A1." Type "Name" and press the "Enter" key on your keyboard. Enter the name of each employee in column A.

- 2
Click cell "B1" and type "Tot Hrs." Press the "Enter" key. Enter the total number of hours for each employee in column B.

- 3
Click cell "C1" and type "Hr Rate." Press "Enter" and enter each employee's hourly rate in column C.

- 4
Click cell "D1" and type "Reg Hrs." Press "Enter."

- 5
Enter "=IF(B2<40,B2,40)" in Cell "D2" and press "Enter. This formula calculates each employee's regular hours.

- 6
Click Cell "D2" and hover your mouse over the lower-right corner of the cell. Your mouse changes to a "+" sign. Click the corner and drag your mouse down to copy the formula for each employee.

- 7
Click Cell "E1" and type "OT Hrs." Press "Enter."

- 8
Enter "=IF(B2>40,B2-40,0)" in Cell "E2" and press the "Enter" key. This formula calculates the number of overtime hours for each employee.

- 9
Click Cell "E2" and hover your mouse over the lower-right corner of the cell. Your mouse changes to a "+" sign. Click the corner and drag your mouse down to copy the formula for each employee.

- 10
Click Cell "F1" and type "Reg Pay." Press "Enter."

- 11
Enter "=D2*C2" in Cell "F2" and press "Enter." This formula calculates each employee's regular pay for the week.

- 12
Click Cell "F2" and hover your mouse over the lower-right corner of the cell. Your mouse changes to a "+" sign. Click the corner and drag your mouse down to copy the formula for each employee.

- 13
Click Cell "G1" and type "OT Pay." Press "Enter."

- 14
Enter " =E2

*(C2*1.5)" in cell "G2" and press "Enter." This formula calculates each employee's overtime pay for the week. - 15
Click Cell "G2" and hover your mouse over the lower-right corner of the cell. Your mouse changes to a "+" sign. Click the corner and drag your mouse down to copy the formula for each employee.

- 16
Click Cell "H1" and type "Tot Pay." Press "Enter."

- 17
Enter " =F2+G2" in Cell "H2" and press "Enter." This formula calculates the total amount of each employee's pay.

- 18
Click Cell "H2" and hover your mouse over the lower-right corner of the cell. Your mouse changes to a "+" sign. Click the corner and drag your mouse down to copy the formula for each employee.

- 19
Click Cell "A1" and drag your mouse to Cell "H1."

- 20
Right-click and click "Format Cells." Click the "Font" tab and click "Bold" from the "Font Style" section.

- 21
Click the "Border" tab and click the "Underline" box. This box is in the lower-left corner of the "Border" section. Click the "OK" button to close the dialogue box.

- 22
Click Cell "C1" and drag your mouse down to highlight each employee's hourly rate.

- 23
Right-click and click "Format Cells." Click the "Number" tab and click "Currency." Click the "OK" button to close the dialogue box. Apply this formatting to the amounts in column F, G and H.