free timesheet

Excel Timesheets Add & Convert General Knowledge
Part A. Create a basic Excel Timesheet Add up Hours in Excel How does excel calculate hours
Part B. Insert Unpaid Lunch Breaks Convert Conventional Hours into a Decimal # How does excel calculate dates
Part C. Calculate Overtime Pay Convert Hours and Minutes into Minutes Express Log in and Log out in Excel
Timesheets for Night Shifts Average Hours ignoring Zero's and Error Values Entering only the last 2 digits of a year

Part C. Calculate Overtime Pay Create an Excel Timesheet that calculates overtime hours.


 

Step 1:

Create a basic timesheet as described in  Part A.
Add lunch breaks if desired, as described on Part B.

Step 2: Data Entry

In Cell J1 : type employee's hourly rate
In cell J2: type employee's overtime rate.

Change the Total Hours column to read: Regular Hours (Column F)
Add a new column called: Overtime Hours (Column G)
To simplify, you might want to remove the "Total Pay" Column

Format Columns F & G to "Number" with 2 Decimal places

 Step 3: Calculate Hours worked

In Cell F2, type:     =IF((((C2-B2)+(E2-D2))*24)>8,8,((C2-B2)+(E2-D2))*24)
In cell G2, type        =IF(((C2-B2)+(E2-D2))*24>8, ((C2-B2)+(E2-D2))*24-8,0)

Step 4: Drag down and total

In cell F10, sum regular hours, =SUM(F2:F8)
In cell G10, sum overtime hours =SUM(G2:G8)

Step 4: Calculate Pay:

 In cell F11, type: =SUM(F10*$J$1)
In cell F12, type =SUM(G10*$J$2)

Sum F11 and F12 for total pay.  Type  =SUM(F11+G11)

Date

Log in 

Lunch Starts

Lunch Ends

Log Out

Regular Hours

Overtime Hours

Monday

9:00 AM

1:00 PM

1:30 PM

6:00 PM

8.00

0.50

Tuesday

9:20 AM

11:57 AM

12:15 PM

6:00 PM

8.00

0.37

Wednesday

9:00 AM

12:00 PM

12:32 PM

5:15 PM

7.72

0.00

Thursday

 

 

 

 

0.00

0.00

Friday

 

 

 

 

0.00

0.00

Saturday

 

 

 

 

0.00

0.00

Sunday

 

 

 

 

0.00

0.00

Total Hours

23.72

0.87

Pay

$260.88

$14.30

Total Pay

$275.18

I've seen the future and it's Excel-Shaped.


Template Library Confused? Download our ready to use Excel Templates to add up your hours.
See our Templates

Time Card Calculator Free Online Timecard Calculator. Easy and fast to calculate timesheets.
Try it out!

excel how-to Learn how to add up hours and create your own templates in ExcelExcel Training