If you're not accustomed to working with spreadsheets, the mere thought of creating a timesheet with Excel may be a bit intimidating. It’s for that reason that I decided to create this lighthearted beginners guide on how to create a timesheet with Excel 2003. After completing this tutorial, you should have a solid understanding of the process. So, if you’re ready to cast your inhibitions to the wind, in the words of Michael Buffer, "Let's get ready to rumble!"

This tutorial is made up of concise, easy to understand explanations. There aren’t any complicated formulas or advanced mathematics to learn. The only math tool that you'll utilize is the Excel Sum Function (it's as easy as pie).

Also, this time sheet is designed to calculate hours that fall within a 24 hour period (one day). If you're interested in calculating hours beyond 24 hours, you'll need to use an additional copy of the time sheet. For instance, if you start work at 12:00 p.m. and punch out at 3:00 a.m. the following day, you'll need to use an additional time sheet to calculate the hours from 12:00 a.m. to 3:00 a.m.

For your convenience, I created a blank time sheet template that’s designed to help you follow along with each of the examples in this lesson (you'll need the template in order to complete this tutorial). In fact, I've virtually done all the work for you. The idea here is to simply follow along with the examples and screen shots. Click Here to Download the Template!

Commercial Version of the Time sheet (training and support provided)

There's also a commercial version of the time sheet that can be utilized for training, contractors, small businesses, etc. The commercial version uses Drop Down Windows and Data Validation to protect the contents of vital cells. Overtime hours are also calculated.

THERE ARE ACTUALLY THREE VERSIONS OF THE TIME SHEET.

The time sheet on the first tab calculates hours in 5 minute increments. The time sheet on the second tab calculates in 10 minute increments. Finally, the time sheet on the third tab calculates time in 15 minute increments. The password to unlock the cells is "password" (without the quotation marks). If you decide to purchase the commercial version of the time sheet and need support, I can reached by email at BryanEaddy@aol.com or at MyComputerAid@gmail.com. You can also leave a comment at the end of this hub. Support is provided for free. If you'd like to purchase the time sheet ($4.00), click on the following link.

View the Commercial Version of the the Time Sheet.

Although it's somewhat unconventional to start creating a timesheet in this manner, this approach conserves time.

Enter the following data onto your timesheet exactly as it's appears below. Enter only the data that's displayed in bold text. Excel will automatically reformat the information for you.

1. In Cell B3 enter: 01/17/09 (hit the tab key)
2. In Cell C3 enter: 8:00 AM (hit the tab key)
3. In Cell D3 enter: 5:00 PM (hit the tab key)
4. In Cell E3 enter: 12:00 PM (hit the tab key)
5. In Cell F3 enter: 1:00 PM (hit the tab key)
6. In Cell G3 enter: =SUM(F3 – E3) (hit the tab key)
7. In Cell H3 enter: =SUM(D3 – C3) – G3 (hit the enter key)
8. In Cell H11 enter: =SUM(H3:H9) * 24 (hit the enter key)

Your timesheet should resemble the following:

At this point, you could simply copy the data and functions from row 3; paste them onto the subsequent rows, and your timesheet would be complete. However, because it's important to gain a solid understanding of the process, we'll review a few timesheet principles.

Important note: In regard to the training template; the cells have already been formatted. The following information is simply a review of the process, and an explanation as to why the cells needed to be formatted. As I mentioned earlier, because most of the work has been done for, you can simply follow along with the examples and screenshots (I wish that everything in life was this simple).

Why Format the Cells?

Because Excel deals with dates and times in a very unique manner, cells that contain time related data or functions should be formatted appropriately. For instance, if a time related cell is formatted as a general number with 2 decimal places, 8:00 is represented as 0.33 and 1:00 is represented as 0.04. This is because excel observes time as a decimal fraction, or a fractional portion of a 24 hour day. In this next section, you’ll see how the cells in the template were formatted.

The cells that were individually formatted are:

• The Date column cells
• The Time-In, Time-Out, Out to Lunch and Return column cells (the Return column represents the return from lunch time)
• The Lunch Period Total and Number of Hours column cells
• Cell H11 (cell H11 is used to tally the total number of hours worked for the week)

Steps Taken to Format the 'Date' Column:

1. Click on cell B3; hold down the left mouse button.
2. Drag the mouse pointer down to cell B9.

In Excel the highlighted area is represented as B3:B9 (Displayed below).

Click on the Excel Format menu option; select Cells… from the drop down menu (displayed below).

When the Format Cells window pops-up (displayed below), click on the Number Tab and select the options that are highlighted in blue:

Steps Taken to Format the 'Time-In', 'Time-Out', 'Out to Lunch', and 'Return' Columns:

1. Click on cell C3; hold down the left mouse button.
2. Drag the mouse pointer down to cell C9; continue to hold down the left mouse button and drag the mouse pointer over to cell F9.

In Excel the highlighted area is represented as C3:F9 (Displayed below)

Click on the Format menu option; select Cells… from the drop down menu (displayed below).

When the Format Cells window pops-up (displayed below), click on the Number Tab and select the options that are highlighted in blue:

Formatting the 'Lunch Period Total' and 'Number of Hours' Columns:

1. Click on cell G3; hold down the left mouse button.
2. Drag the mouse pointer down to cell G9, continue to hold down the left mouse button and drag the mouse pointer over to cell H9.

In Excel the highlighted area is represented as G3:H9 (Displayed below).

Click on the Format menu option; select Cells… from the drop down menu (displayed below).

When the Format Cells window pops-up (displayed below), click on the Number Tab and select the options that are highlighted in blue:

Formatting Cell H11 (used to tally the Total Number of Hours Worked for the Week):

1. Click on cell H11.
2. Click on the Format menu option; select Cells from the drop down menu (displayed below).

When the Format Cells window pops-up (displayed below), click on the Number Tab and select the options that are highlighted in blue. Remember to also enter the number '2' in the Decimal Place window (you can also click on the Use 1000 Separator (,) check box if you like).

Congratulations! You’ve reviewed the complete process of how to create a timesheet with Excel. You may want to examine the functions as they're relatively easy to understand. A basic Sum Function is utilized to calculate working hours. The last function in cell H11 calculates the total number of hours, and thereafter multiples those hours by 24 (24 represents the number of hours in a day). If you have any questions or need assistant, please leave me a comment and I'll respond promptly. Thanks!