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.
how to Calculate Overtime in excel
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((((C2B2)+(E2D2))*24)>8,8,((C2B2)+(E2D2))*24)
In cell G2, type =IF(((C2B2)+(E2D2))*24>8, ((C2B2)+(E2D2))*248,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 ExcelShaped.
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 howto Learn how to add up hours and create your own templates in ExcelExcel Training