How to calculate time & overtime in excel

Written by angela m. wheeland Google
  • Share
  • Tweet
  • Share
  • Email

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

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

  4. 4

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

  5. 5

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

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

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

  8. 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. 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. 10

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

  11. 11

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

  12. 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. 13

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

  14. 14

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

  15. 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. 16

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

  17. 17

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

  18. 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. 19

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

  20. 20

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

  21. 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. 22

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

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

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.