Learn How to create a timesheet in excel- Free guide on excel timesheets
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(C2-B2)*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((C2-B2)+(E2-D2))*24
Time worked before lunch plus time worked after lunch
Method B:
=SUM((E2-B2)-(D2-C2))*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((((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.
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(B1-A1)*24
Format as number
Method B:
In cell C1, enter:
=(IF(A1>B1,B1+1-A1,B1-A1))*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:
=(B1-A1+(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.