Skip to main content

Legal

Tax & Accounting

Trade & Supply

Risk & Fraud

News & Media

Books

Developers

Enter payroll checks

Import time entry data from a spreadsheet

You can import time entry data from a spreadsheet (in xls and xlsx format) in the Enter Batch Payroll Checks screen.

Special information

  • The spreadsheet to import will need to contain the following column labels
    • Employee ID.
       This must be the 1st column in the spreadsheet.
    • Location.
       The location column must be placed directly before the Department column.
    • Department.
       The department must be defined if employees are assigned to multiple departments.
    • Pay item.
       There must be at least 1 pay item.
  • The names of the locations and departments listed in the spreadsheet are case-sensitive and must exactly match the names in the application for the client.
  • Employees who are assigned to more than 1 department or location must be listed on a consecutive line in the spreadsheet for each department or location they're assigned to. For example, if Employee 100 is in Department 1 and Department 2, then Employee 100 is listed in a spreadsheet row for Department 1 and listed again in the next row for Department 2.

Set up your client for time entry via a spreadsheet import

The time entry spreadsheet import wizard is available only for payroll schedules that are set up to use the
Excel time entry
 method.
To modify a client's existing payroll schedule to use the
Excel time entry
 method, follow these steps.
  1. Select
    Setup
    , and then
    Clients
    .
  2. Make sure the client is selected, select
    Edit
    , and then go to the
    Payroll Information
     tab.
  3. In the Payroll Schedules section, select the ellipsis.
  4. Select the schedule to change and then select
    Edit
    .
  5. In the Time Entry Method section, select
    Import
    .
  6. In the
    Source
     dropdown, select
    Excel time entry
    .
  7. Select
    Enter
    , then
    Done
    .
  8. Select
    Enter
     to save the changes to the client.

Import time entry data from a spreadsheet

  1. Make sure the spreadsheet is closed and not password protected.
  2. Select
    Actions
    , then
    Enter Batch Payroll Checks
    .
  3. Select the client and the payroll schedule.
  4. The Spreadsheet Import wizard opens. Select the location of the spreadsheet to import.
  5. In the
    Worksheet to import
     field, select the worksheet name that has the data.
  6. Select
    Next
     to continue.

Map columns in the spreadsheet

  1. If this isn't your first time importing a spreadsheet with time entry data, you can select a predefined mapping template from the
    Template
     field. If there isn't anything to select in this field, you can create your own mapping.
    • Templates are user-defined and categorized based on the import type that's selected in the Spreadsheet Import wizard.
    • The
      Template
       field always defaults to
      <None>
      .
    • Templates include all predefined column assignments in addition to any rows that are marked to be omitted.
    • You can right-click a template in the
      Template
       field to add, edit, or delete the item.
  2. For each column in the spreadsheet grid, you can select an item from the dropdown list.
  3. Mark the
    Omit Row
     checkbox for any rows that don't contain data that should be imported.

Time entry scenario

Usually, the client gives the accountant a spreadsheet of the client's employees by name and/or ID with the hours they worked in each department for each pay item. Instead of entering hours manually for individual employees, you can import the data for all employees at the same time. Using this method, you can also specify fixed dollar amount overrides for deduction items.
If you're importing time entry data, you may want to define columns for employee ID, employee name, regular hours, overtime hours, pay rate, accruable benefits, and so forth. It's only necessary to map pay and deduction items for specified columns to the corresponding payroll items for the 1st import or when new (undefined) pay or deduction items are present in the spreadsheet. Because the client's spreadsheet format remains consistent across multiple imports, it's easy for the accountant to set up a template with predefined columns for ease of import. A typical time entry spreadsheet could look like this.

Sample spreadsheet file

We've created a sample spreadsheet file for you to download and review. The sample spreadsheet is set up with commonly used columns and some sample data. You can change the formatting, columns, and data to fit your needs.

This article applies to:

  • Product: Accounting CS
  • Subject: Import

Related content

Chat now

error-icon

Triva isn't available right now.

Check out the support page for our phone number and hours

error-close