excel timesheets - time card Template

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

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

 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.