DISCOVER
×

How to calculate time & overtime in excel

Updated March 20, 2017

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.

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.

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

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

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

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

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.

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

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.

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.

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

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

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.

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

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

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.

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

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

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.

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

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

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.

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

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.

bibliography-icon icon for annotation tool Cite this Article

About the Author

Angela M. Wheeland specializes in topics related to taxation, technology, gaming and criminal law. She has contributed to several websites and serves as the lead content editor for a construction-related website. Wheeland holds an Associate of Arts in accounting and criminal justice. She has owned and operated her own income tax-preparation business since 2006.