Learn how to create an Excel Timesheet  Free guide on Excel Timesheets
Part A. Create a basic Excel Timesheet
Part B. Insert Unpaid Lunch Breaks
Part C. Calculate Overtime Pay
Light, easy training on calculating hours and minutes in excel
Part A. how To create a basic Excel Timesheet:
Step 1: Data Entry
On Column A: Enter the days of the week
On Column B: Enter the time work started, in an hh:mm format
You may use AM/PM or military time, but be consistent within your timesheet
On Column C Enter the time work ended
Step 2: Calculate hours worked
On column D: Type: =SUM(C2B2)*24
Change the cell format to "Number" with 2 decimal places.
Step 3: Calculate Pay
Cell H1 Type the hourly pay of that employee
On Column E: =SUM(D2*$H$1)
(The $$ will prevent the cell number to roll over when you copy the formula down through the days of the week)
Step 4: Drag down and finish
Drag the formulas of Column D & E through the days of the week
Cell D9: For total hours, use =SUM(D2:D8)
Cell D10: For total pay, use =SUM(E2:E8)
Date 
Log in 
Log Out 
Total Hours 
Total Pay 

Monday 
9:15 AM 
5:02 PM 
7.78 
$77.83 

Tuesday 
9:20 AM 
5:00 PM 
7.67 
$76.67 

Wednesday 


0.00 
$0.00 

Thursday 


0.00 
$0.00 

Friday 


0.00 
$0.00 

Saturday 


0.00 
$0.00 

Sunday 


0.00 
$0.00 

TOTAL 
15.45 
$154.50 

I am stuck on Excel, 'cause Excel's Stuck on me.
Part B. Insert Unpaid Lunch Breaks
Step 1:
Create a basic timesheet as described in Part A,
Step 2: Data Entry
Add to columns between the Log In column and the Log out Column
Name them: Lunch Starts and Lunch Ends
Step 3: Calculate Hours worked
In cell F2 type
Method A:
=SUM((C2B2)+(E2D2))*24
Time worked before lunch plus time worked after lunch
Method B:
=SUM((E2B2)(D2C2))*24
All time between Log in and Log out, minus lunch time
Date 
Log in 
Lunch Starts 
Lunch Ends 
Log Out 
Total Hours 
Total Pay 
Monday 
9:15 AM 
11:50 AM 
12:20 PM 
5:02 PM 
7.28 
$80.12 
Tuesday 
9:20 AM 
11:57 AM 
12:15 PM 
5:00 PM 
7.37 
$81.03 
Wednesday 
9:00 AM 
12:00 PM 
12:32 PM 
5:15 PM 
7.72 
$84.88 
Thursday 
9:00 AM 
1:00 PM 
1:30 PM 
5:00 PM 
7.50 
$82.50 
Friday 




0.00 
$0.00 
Saturday 




0.00 
$0.00 
Sunday 




0.00 
$0.00 
Excel Unscripted.
part c. Excel Timesheets with Overtime
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.
Calculate Night Shifts in Excel
Excel cannot handle negative times. If you start working at 11:00 pm and finish at 7:00 am, Excel doesn't understand it's a new day.
Here are 3 methods on how to explain Excel that you finished working at 7:00 am of the following day.
Method A:
Enter the full date and time you logged in and logged out.
In cell A1, enter: 6/1/2008 11:00:00 PM
In cell B1 enter: 6/2/2008 7:00:00 AM
In cell C1, enter: =SUM(B1A1)*24
Format as number
Method B:
In cell C1, enter:
=(IF(A1>B1,B1+1A1,B1A1))*24
Format the cell to "Number".
You are telling excel:
If A1 (11:00 pm) is bigger than B1 (7:00 am), than add one day to B1 (7:00 am), and only after that subtract it from 11:00 pm.
However, if that's not the case, than simply do B1  A1
When you are done, multiply my total by 24.
Method C:
=(B1A1+(B1<A1))*24
You are telling excel to:
"Subtract B1 from A1", plus:
"B1 (7:00 am) is smaller than A1 (11:00 pm)". Is it true?
If it's true or One, add 1 (one day)
If it's False or Zero, add zero.
Multiple my total by 24.
Think Different, think Night shifts.