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