Timesheet Templates
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

Timesheets for Night Shifts

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.

Return to main menu

 

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.

Return to main menu

 

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.

Return to main menu

 

 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.

Return to main menu