How to Calculate Time & Overtime in Excel

By Angela M. Wheeland

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, analyzing 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.

Step 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.

Step 2

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

Step 3

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

Step 4

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

Step 5

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

Step 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.

Step 7

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

Step 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.

Step 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.

Step 10

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

Step 11

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

Step 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.

Step 13

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

Step 14

Enter " =E2*(C2*1.5)" in cell "G2" and press "Enter." This formula calculates each employee's overtime pay for the week.

Step 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.

Step 16

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

Step 17

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

Step 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.

Step 19

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

Step 20

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

Step 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 dialog box.

Step 22

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

Step 23

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