Related Articles

Employees who earn hourly wages may be subject to the Fair Labor Standards Act, administered by the U.S. Department of Labor, which regulates wages and overtime pay. The law requires that employers track their workers' daily and weekly work hours. To simplify your recordkeeping and provide input for business accounting in Intuit QuickBooks, you can use Microsoft Excel to create virtual time cards that record each employee's work schedule. Once you set up a standardized Excel workbook, you can reuse it to cover every work period, with an individual worksheet representing each employee's performance.

1.

Switch to the "View" tab of the Microsoft Excel ribbon, click the "Freeze Panes" item to open its drop-down menu, and then select "Freeze Top Row" to keep the initial row of your worksheet visible regardless of how far down the sheet you scroll.

2.

Enter column headings in row A, beginning with "Date" in cell A1. Set up columns for as many workday milestones as your employees use. If they clock in at the beginning of the day and clock out at the end, you need only two columns for time entries. If they clock out and in for break times and meals, you need a column for each of these significant time points. After you enter all the column headings for day subdivisions, title the next two columns "Daily Hours" and "Weekly Hours."

3.

Click on the header for column A to select all its cells. Switch to the "Home" tab of the Excel ribbon, and then click the unlabeled dialog box launcher at the bottom of the Number group, marked with an angled arrow. When the Number dialog box opens, click the "Date" item in the category list, and then select your preferred date format from the Type list. Click the "OK" button.

4.

Click on the header for column B -- the first time-tracking column -- and "Shift"-click on the header of the last time-tracking column you created. Click the dialog box launcher in the Home tab of the Excel ribbon's Number group, select the "Time" item in the category list, and then apply the format identified in the Type list as "1:30 PM" to display times with daypart indicators. Click the "OK" button to complete the formatting process.

5.

Click the header of the daily time-totaling column, and then "Shift"-click on the header of the weekly-total column you labeled last, selecting all the cells in these columns. Click the Number dialog box launcher a third time, choose the "Custom" item in the category list, and then enter "[h]:mm" (without the quotes) in the Type field. This custom number format accommodates time values that exceed 24 hours in duration, enabling you to sum a week's work and display the result correctly.

6.

Click in the time-totaling cell that tracks daily hours. Type an equal sign to signal to Excel that you're entering a formula, click on the end-of-day clock-out cell in that row to add its location to the formula, type a minus sign, and then click in the clock-in cell. Press "Enter" to complete the formula, which subtracts the clock-out time from the clock-in time. If your employees don't clock out for breaks or lunch, this formula calculates work hours for the entire day. Otherwise, your formula needs additional calculations (see Tips).

7.

Click in the week-totaling cell in row 6, which will display the sum of a full week's work hours, and then type "=SUM(" (without the quotation marks). Click in the day-totaling cell in row 2, drag down through the column until you highlight the cell in row 6, type a closing parenthesis, and then press the "Enter" key to complete the formula.

8.

Click and drag from the first cell in row 2 through the week-totaling cell in row 6, selecting the entire block of cells, and then place your cursor over the lower right corner of the last cell in the selection. When the cursor changes appearance to look like a black plus sign, drag downward to autofill a year's worth of rows, stopping after you fill row 261.

9.

Double-click the worksheet's title tab to select the placeholder text that identifies the sheet. Enter the name of the first employee whose time you need to track.

10.

Click on the intersection of the worksheet's numbered row and lettered column headers to select the entire data area, and then press "Ctrl-C" to copy the selection to the clipboard. Click on the plus sign next to the current worksheet's name tab to insert a new worksheet.

Tips

To accommodate a clocked-out lunch period, click in the daily time-total cell, and then type "=SUM(" (without the quotation marks) to begin the formula. Click in the lunch clock-out cell, and then type a minus sign; click in the morning clock-in cell, and then type a plus sign; click in the day-ending clock-out cell, type a minus sign, and then click in the lunchtime clock-in cell. Complete the formula with a closing parenthesis, and then press "Enter."

To import Excel data into QuickBooks, you can copy from your worksheet and paste directly into the QuickBooks Add/Edit Multiple List Entries window, set up a special spreadsheet that contains data from your time-tracking worksheets or use versions of the text-based CSV file format to build a file that QuickBooks can ingest.

The empty cells that contain time-tracking formulas display "0:00" until you populate the cells they evaluate.

Some of Excel's time formats display an asterisk in front of their descriptions. These formats use the regional time settings established in the Windows Control Panel.

After you set up your master Excel file, save it as a template so you can reuse blank copies of it each time you open it.

Warnings

If you don't enter "a" or "p" after a time value, Excel automatically treats it as a morning entry and adds "a.m." to it. To simplify time entries, use 24-hour notation, which turns "16:30" into "4:30 p.m." and "16" into "4 p.m."

Information in this article applies to Microsoft Excel 2013. It may differ slightly or significantly with other versions or products.

About the Author

Elizabeth Mott has been a writer since 1983. Mott has extensive experience writing advertising copy for everything from kitchen appliances and financial services to education and tourism. She holds a Bachelor of Arts and Master of Arts in English from Indiana State University.

Photo Credits

Ryan McVay/Photodisc/Getty Images

bibliography-iconicon for annotation tool Cite this Article

Choose Citation Style

Mott, Elizabeth. "How Do I Use an Excel Spreadsheet to Track Employee Time to Import to QuickBooks?" Small Business - Chron.com, http://smallbusiness.chron.com/use-excel-spreadsheet-track-employee-time-import-quickbooks-78176.html. Accessed 15 September 2019.

Mott, Elizabeth. (n.d.). How Do I Use an Excel Spreadsheet to Track Employee Time to Import to QuickBooks? Small Business - Chron.com. Retrieved from http://smallbusiness.chron.com/use-excel-spreadsheet-track-employee-time-import-quickbooks-78176.html

Mott, Elizabeth. "How Do I Use an Excel Spreadsheet to Track Employee Time to Import to QuickBooks?" accessed September 15, 2019. http://smallbusiness.chron.com/use-excel-spreadsheet-track-employee-time-import-quickbooks-78176.html

Note: Depending on which text editor you're pasting into, you might have to add the italics to the site name.