How do I Use an Excel Spreadsheet to Track Employee Time to Import Into Quickbooks?

Written by stacy tabb

Share

Tweet

Share

Pin

Email

Employee time records must be kept on file for three years. (file image by Jan Will from Fotolia.com)

The Fair Labor Standards Act (FLSA) states that employers are required to track the time that employees work each day. This does not apply to employees who are on a salary, but rather those paid by the hour (also known as non-exempt employees). It's important to track non-exempt employee's time, because employers are required to give overtime pay to hourly employees who work more than 40 hours a week. The FLSA doesn't require a specific method for keeping time, but does require that these time records be kept on file for at least three years. An Excel spreadsheet is a simple way to store and track this information.

Skill level:

Moderate

Other People Are Reading

Things you need

Microsoft Excel

Show MoreHide

Instructions

1

Instruct all employees to fill out a standard time card each week. This could be a hard copy (piece of paper), or an electronic timecard template that they can e-mail to you each week. You will need to know the number of hours they worked each day so that you can calculate the hours worked for each week. You may decide that you'd like to know their "time in" and "time out".

2

Open up an Excel spreadsheet. Label Column A "Name". Type each employee's name into each cell under the "Name" column. Some people may find it helpful to type the names in a "First name, Last name" format for easy alphabetical sorting.

3

Label columns 2 through 6 with the dates for the current week (this is assuming you have a five-day workweek. For a six- or seven-day workweek, use more columns. For example, in cell B1, type Oct. 25, in cell B2, type Oct. 26, and so on for the entire week.

4

Label the last column "Total" and leave it blank for now.

5

Label the sheet in this workbook with the first day of the workweek it represents. To do this, bring your cursor to the bottom of the worksheet and double click on "Sheet1". Type "Oct. 25" (or the appropriate date) into the tab.

6

Collect your employee's personal timesheets at the end of every week and input their hours worked into the spreadsheet you created.

7

Once you have entered all employee hours for the week, place your cursor in column B2 and drag it horizontally into the "Total" column, and down as far as the last employee name. Hit the AutoSum button on your toolbar and the total hours worked for that week will be automatically calculated for each employee and displayed in the "Total" column.

8

Copy and paste this worksheet into a new worksheet at the bottom of the page and label it with the following week's dates. Continue to track your employee's time throughout the year, creating a new worksheet for each new week.

Tips and warnings

For ease in labelling the dates, enter the first date into column B, and then click on the cell that you just labelled, place your cursor over the box at the bottom right of the cell, and drag the cell over as many columns as you need labelled. The dates will automatically fill into each cell in progressive order.