Timesheet Link for QuickBooks

Introduction

Thank you for your interest in the Timesheet Link for QuickBooks by Big Red Consulting.

We’ve written this document to ensure you have a smooth start when using the tool and to address frequently asked questions. We recommend printing this document for easy reference. Please review the document completely as it answers most common questions.

QuickBooks is a registered trademark of Intuit, Inc.

MS Excel is a registered trademark of Microsoft, Inc.

The Timesheet Link for QuickBooks, the XL Email Manager, and this document are copyrighted by Big Red Consulting.

Introduction 1

Overview 2

Administrative Features for Timesheet Managers 2

Essential Activities 3

Installing the tool 3

Loading the addin in Excel 3

Trial Period 4

Purchase & Entering your Product Key 4

Moving the tool to another computer 4

Integrating with your QuickBooks file 5

Before you Integrate 5

Integration Options 5

When to Reintegrate 5

Creating Timesheets 6

Emailing Timesheets 8

Custom Templates 9

Creating New Templates 9

To create a custom template 9

Entering Start/Stop times 10

Creating a Custom Time List 10

Mapping Customer Names 11

Mapping Service Items to Payroll Items 12

Automatic Payroll Item selection 12

Automatically Calculating Overtime 13

Limitations & Assumptions 13

Setting up 13

Creating an IIF file 14

Exporting a Time Sheet 14

Exporting multiple Time Sheets for a single QuickBooks import 15

Exporting a Time List 16

Importing your IIF file into QuickBooks 17

Other Common Questions 17

Overview

The Timesheet Link for QuickBooks works within Excel as an Excel “Add-In”.

When installed it loads in Excel in a special way which creates a special menu item. It will create an IIF file containing your time records to import into QuickBooks.

When using the XL Timesheet Link you will:

1.  Create a worksheet that contains time records

  1. This worksheet can be either a time sheet layout with multiple days of time on each row or a time listing layout with each time record on a row and all the applicable fields for the time record in columns.

2.  Export the time to an IIF file

  1. As you export you’re asked to ‘map’ your worksheet columns and fields so the XL Timesheet addin can find your data.

3.  Import the time IIF file into QuickBooks

  1. Once you import, the time will appear in time reports and on time sheets in QuickBooks just as if you’d recorded it manually.

Administrative Features for Timesheet Managers

If you’re a time administrator, and you send timesheets to employees and get time data from them to enter into QuickBooks, this tool has several key features to offer.

  1. By using Excel timesheet, you can shift the electronic time entry from paper to an electronic means. Then you only need to review the time for accuracy instead of re-inputting it, before optionally sending it to QuickBooks.
  2. You can quickly create batch timesheets and either save them to a network drive or directly email them to employees (XL Email Manager required for email integration.)
  3. You can create custom templates based on our original templates to better suit your needs. You can change fonts, add data, and make many other customization options.
  4. Timesheets can use custom templates with default values to make data entry for employees even easier. For example, you can pre-enter Service Items and Jobs and even pre-enter hours worked for full time employees with a regular schedule. Additionally, you can create timesheets with pick-lists for your employees to choose from.
  5. When the employees return the Excel spreadsheet to you, you can review it and then create an IIF file for import into QuickBooks. You can create an IIF file containing just one or many timesheets.

Essential Activities

This is an overview of various utility operations you may want to complete when using the tool. The later sections in this document cover the steps for using the tool.

Installing the tool

If you downloaded the installer and ran it, and are reviewing this document from the Start menu or from within Excel, then the installation is complete. However, you may still need to load the Add-in from within Excel.

If you downloaded the .ZIP file, installation of this Add-In is a matter of copying this document and the *.XLA file (the Excel Add-In) to a convenient folder. We recommend something like “Programs Files\BRC Timesheet”.

Loading the addin in Excel

Sometimes the addin will not load after running the installer. In this case you may load this addin from within Excel manually. This is a one-time process. Once loaded, each time Excel starts the addin will normally be available until you unload it:

To load the addin, first start Excel & make sure a workbook is open (one usually opens when you start Excel.)

Excel 2010 & 2013:

1. Select File, then Options from Excel's menu.

2. In the Options dialog, select Add-ins.

3. At the bottom of the add-ins pane, select Excel Add-ins and press Go.

4. When the Add-ins dialog opens, click Browse and navigate to the folder where you installed the addin.

5. Select the *.xla file and then OK to close the dialogs.

Excel 2007:

1. Click the Microsoft Office Button and then click Excel Options.

2. Click Add-Ins and then in the Manage box select Excel Add-ins. Click Go.

3. In the Add-Ins available box, click Browse... and navigate to the folder where you installed this addin.

4. Select the *.xla file & then OK to close the dialogs.

Excel 2000 to 2003:

1. In Excel choose Tools | Add-ins... from Excel's menu.

2. Click Browse... & navigate to the folder to which you installed this addin.

3. Select the *.xla file & Press OK to close the dialogs.

At this point, you should see this addin listed in the Add-In dialog, with a checkmark next to it. Press OK to close the dialog, which will load the Add-In.

In Excel 2007 - 2013, you’ll see the addin’s access points from the Add-Ins tab. In Excel 2000 – 2003 you’ll see a new menu item appear just to the right of Excel’s help menu.

Trial Period

Once loaded, the tool is in “trial” mode. In trial mode, most features are available, but there are limitations to the number of time records that can be exported. See the About & Purchase dialog for information on the current state of your trial.

Purchase & Entering your Product Key

To purchase the tool, select About & Purchase from the menu. Click the button Purchase, and you’ll be taken to our website to a starting point to make your purchase. At the end of the purchase, you’ll be given a temporary product key with which to fully unlock your tool for seven days.

Once your order is complete, you will receive an email with your receipt & permanent product key.

To enter your temporary or permanent key, choose About & Purchase from the XL Timsheet menu, enter the key into the Key field, and then press the I Accept button to unlock the product.

Note: You may copy the key from the email or web page and paste it in the key field: Use Ctrl-C (or right-click Copy) to copy the key & then paste it into the About & Purchase dialog using the shortcut key Ctrl-V.

Moving the tool to another computer

To move the tool to another computer,

  1. Locate your tool’s key code. If you don’t have a copy of the email, you can copy the key out of the tool’s About & Purchase dialog.
  2. Install the tool on the new computer.
  3. Enter your product key into the tool on the new computer.

Integrating with your QuickBooks file

In order for the tool to create a timesheet with pick lists, and to make sure your time IIF file will import into QuickBooks without error, it must be taught about your QuickBooks lists. This is done by integrating the XL Timesheet Link with QuickBooks.

To integrate with your company file, pick the first option from the menu “Integrate QuickBooks Lists.” Review the instructions there to complete the process. When you do this various ‘lists’ are pulled from QuickBooks and kept locally for reference as you create timesheets and IIF files.

Before you Integrate

Before Integrating, make sure you have setup list records in QuickBooks to be used on timesheets and when creating IIF files with your time records.

In QuickBooks, you should have created:

-  Employees you’ll track time for

o  If employees will use time for payroll, be sure to setup earnings items on the employee records.

o  If you’ll be tracking overtime and double-time, be sure to setup at least one of each of these types of payroll earnings items.

-  Vendors for which you want to track time, possibly marked as 1099 vendors if you want to restrict the vendor list.

Integration Options

When you integrate, you can choose from several options on the Options tab:

-  Whether or not to include all Vendors, only 1099 Vendors, or no Vendors on the list of timesheet names.

-  Whether or not to include Overtime and Double-time items on the Payroll Item pick lists.

When to Reintegrate

Reintegrate whenever list records you use on your timesheets have changed so that the XL Timesheet addin can make IIF files that will import without error. For example, if you change employee or vendor records in a material way, the XL Timesheet addin needs to know about these changes.

Specifically, reintegrate after you:

-  Add new employees you want to create time for.

-  Change an employee’s name in QuickBooks.

-  Change the earnings item an employee uses.

-  Change the ‘use time for payroll’ setting on an employee

-  Change a vendor’s name

-  Add or change hourly or salary payroll item names.

-  Add or change class records

-  Add or change service items

Creating Timesheets

Use the menu item Create New Timesheets & Time Lists to create one ore more timesheets. You’ll be given several options illustrated below:

Fig 3: The tool’s Create Timesheets dialog

Notes

·  Using this dialog, you can create either one or several timesheet at a time.

·  When creating several timesheets, it is often useful to save them all to a specific folder, for further processing or emailing to employees.

·  Be sure to note the Columns and Pick lists tabs, which offer additional options.

·  Select the number of rows and columns to show and the default ending date.

·  Once created, timesheets work independently and can be emailed, copied, modified, and used week after week.

·  If you pick the option to include pick lists, you’ll see lists of your active imported names in the resulting worksheet:

Fig 4: An example timesheet created by this tool.

Emailing Timesheets

As seen in Fig 3, you also have an option to email new timesheets to your employees. This features works in conjunction with our XL Email Manager for Excel, which turns Excel into an email client and “mail merges” information from Excel to lists of email addresses.

When the XL Email Manager is installed on your system (and registered, if this tool is registered) you can email timesheets to your employees. A special UI and merge options are available from this tool, but email setup account setup is done through the XL Email Manager.

To email timesheets, first install the XL Email Manager, which you can download from our website. Then check the option as seen in Fig 3, and click email Settings. You’ll see a dialog like this:

Fig 4.1: The email configuration dialog.

Additional considerations:

  1. Before emailing timesheets, do a test run and make sure that that resulting timesheets are what your employees should see. Do this by un-checking the option to email (first) and then selecting just yourself as the employee and sending an email to yourself as a test (you may need to add yourself in a QuickBooks as an employee to do this.)
  2. In QuickBooks, be sure to check the email addresses for the employees who will receive timesheets. The tool uses the addresses imported from QuickBooks.
  3. You can only email timesheets if you select the option to save the timesheets. This is so there is an actual file to attach to the email.

Custom Templates

The Timesheet tool supports custom templates. Using the Manage Templates feature you can create new templates based on the tool’s existing templates.

Creating New Templates

To create a new template, first launch the Manage Templates feature. You’ll see a dialog that looks like this:

Fig 5: The Manage Template dialog.

To create a custom template

First Edit an existing template and then Add it with a new name. When you pick Edit, the template is displayed for you to change. Then, reopen the window above and Add it.

You may make the following changes to a template:

·  Fonts

·  Color and Shading

·  Change any text on the template

·  Add default values in the various columns and time entry fields

·  Add rows of data before or after the data entry area