How to Calculate Time & Overtime in Excel

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

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

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

Step

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

Step

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

Step

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

Step

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

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

Step

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

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

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

Step

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

Step

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

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

Step

Enter " =E2_(C2_1.5)" in cell "G2" and press "Enter." This formula calculates each employee's overtime pay for the week.

Step

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

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

Step

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

Step

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

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

Step

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

Step

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

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

Step

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.