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.
note
  • Select the arrow buttons to expand or collapse the Navigation pane in the Spreadsheet Import Wizard.
  • You can't import data from a spreadsheet that was created in a version of Microsoft Excel that is more recent than the version installed on your computer.
  • You might receive an error message if there are blank cells in the spreadsheet that contain spaces. Open the spreadsheet with Microsoft Excel and remove those spaces before importing the spreadsheet again.
  • If an employee pay rate in the spreadsheet is different from the rate for the employee in the application, the import process will update the employee's pay rate in the application that will be used for the current and any future payroll checks.

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.
note
  • Employee ID must be the 1st column in the spreadsheet.
  • All columns are mapped as
    Not Used
     by default. Any columns mapped as
    Not Used
     aren't imported.
  • You can change data by selecting the cell and changing the data.
  • You can delete rows by selecting the blank cell to the far left of the row and then press Delete on your keyboard.
  • If an employee pay rate in the spreadsheet is different from the rate for the employee in the application, the import process will update the employee's pay rate in the application, to be used for the current and any future payroll checks.

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.
note
If you import the sample data into a live client record, remember to delete the imported data when you're finished.
For internal use only
If the spreadsheet needs to be imported again (for whatever reason), you can't simply cancel the batch because of how Accounting CS handles spreadsheet imports in the background.
Follow these steps to cancel the batch and re-import the spreadsheet.
  1. Suspend the batch.
  2. Pull up the batch again by selecting the payroll schedule.
  3. Cancel the batch.
  4. The next time you select that payroll schedule, the Spreadsheet Import wizard should display as expected.

This article applies to:

  • Product: Accounting CS
  • Subject: Import

Related content

error-icon

Triva isn't available right now.

Check out the support page for our phone number and hours

error-close