Excel time entry import example

Show expandable text

Warning

Product support for the Creative Solutions Accounting platform ended on September 30, 2020.

Help & How-To Center content for the Creative Solutions Accounting platform may be outdated and is used at your own risk.


In this example, we need to import time entry information from an Excel spreadsheet for a number of employees. We will do this using the Excel time entry import utility. Note that to run this import utility, you must be using Microsoft Office 2000 or higher.

See also: Importing employee time entry from an Excel spreadsheet

Requirements for Excel spreadsheet import

For the import to be successful, the data must be set up in the spreadsheet in a particular way and meet the following criteria.

  • The first column of data in your import file must contain the employee ID number.
  • The second column of data must contain the employee's payroll department.
  • There can be no blank rows between employee records.

Notes

  • Hours must be entered into the spreadsheet in decimal format.
  • If an employee has time entry information for more than one department, enter time entry information for each department on a separate line of the spreadsheet. Enter an asterisk (*) in the Employee ID column to indicate that a row of information is a continuation from the row above (same employee, different department) and that it should be entered on the same check.
  • If an employee is to receive multiple checks from information entered in the same spreadsheet, enter the employee's ID in each row of check information.

For an example of how information should be presented in the spreadsheet, see the following diagram. Note that Employee ID 5 is to receive two checks - one with hours in two departments and one with hours in the default department.

/_images/acct_pr/csa/exceltime_xls_circled.gif

The first line of each column specifies the header for each column. While this is not required, it will make the process of specifying column titles within the application much easier.

Top of page

Specifying the Excel spreadsheet to import into CSA

  1. Choose Tasks > Payroll Check Entry.
  2. In the Payroll Check Entry window, choose Edit > Excel Time Entry Import.
  3. In the Excel File Location dialog, enter the location or browse for the drive and folder where your spreadsheet is located, and then click OK.

    /_images/acct_pr/csa/exceltime_filelocation.gif

  4. In the Excel Time Entry Import dialog, you may select a previously saved setting to be used for this import.

    Note: Once you have formatted the columns the way you want, you will have the option to save this column arrangement (setting) for future imports, and that setting name will automatically display as an option in the F4 drop-down list.

  5. Because our example spreadsheet uses the first row for column headers, we need to mark the First row header checkbox.

    /_images/acct_pr/csa/exceltime_importdialog.gif

Top of page

Specifying column titles

The column titles specify the type of data to be entered into the Payroll Check Entry window.

Notes

  • Each column title must be unique.
  • You can use any column order within the grid. If each column title has been identified correctly, the data will flow to the appropriate fields within the Payroll Check Entry window.
  1. The first two columns have already been specified as the Employee ID and Payroll department columns as required. Right-click the third column header, and choose Edit Column Title from the context menu.
  2. In the Column Title dialog, choose the category of information appropriate to the first column. In our example, this would be Pay Items.
  3. Highlight Salary in the Column Titles list.
  4. Select the appropriate type for the hours from the Type drop-down list - in this case, Reg hours.

    /_images/acct_pr/csa/exceltime_columntitle.gif

  5. Click OK. The column titled C: Not Used now specifies the column as Salary Reg hours PAY.
  6. Continue in the this way to specify all the columns of information. For the Wages hours column, choose Pay Items, Wages, and Reg hours as the type. For the Wages OT hours column, choose Pay Items, Wages, and OT hours as the type, etc.
  7. When we get to the 401(k) column, we will select Deduction Items and then highlight 401(k) % of Gross in the Column Title list. The Type field is not available for deduction items.

    /_images/acct_pr/csa/exceltime_columntitle_ded.gif

  8. Click OK.

    /_images/acct_pr/csa/exceltime_importdialog401k.gif

  9. Click OK to complete the import.

Top of page

Saving the spreadsheet setting

  1. The application prompts you to save a unique name for the spreadsheet (setting). Click Yes. This makes all the setup information and column headings available for a future import.
  2. In the Save Spreadsheet Setting dialog, enter a name for the spreadsheet setting, for this example enter Excel Time Import 1, and then click Save.
  3. In the Recurring Information For Import Checks dialog, enter any information that should be applied to each check created by the import.

    Note: Some fields may be inactive. If a field has been disabled in the Payroll Check Entry Options dialog, it will be inactive in the Recurring Information For Import Checks dialog.

Top of page

Saving or abandoning the import

The Excel Time Entry Import dialog displays, listing any problems encountered with the data, and listing all checks that were imported.You can choose to Save the import, or abandon it at this point.

  • If you click the Abandon button, the import is abandoned but the spreadsheet setting is saved.
  • If you click the Save button, the checks are imported, the spreadsheet setting is saved, and unprinted checks are created in the Payroll Check Entry window.

Top of page

Was this article helpful?

Thank you for the feedback!