WELCOME TO GMS ACCOUNTING ONLINE HELP
The help you need at your fingertips.
You are here: Payroll > Payroll Supplements > #411 Timesheet Import
#411 Timesheet Import
Function
The Timesheet Import supplement allows you to take spreadsheet files or a file formed in time clock software and form a timesheet batch. The files must be set up in a standard format.
Things You Should Know
File Format
When developing the timesheet batch, the software is performing the following checks, and prepares a list of items that were found that can be displayed or printed. If codes from the file are not found in the database, it will stop the process of importing and give you a list of items found. These will need to be corrected in the original file.
--If you are importing timesheets for employees that have a pay period (PP) rate in their employee file or "Ask for hours" is checked, it will stop and ask how many hours that employee worked for this pay period. Those employee’s hours should be noted prior to doing the import.
--When importing from Excel, the 300th record must not be blank and the file cannot exceed 500 records.
--If an Element in the file to be imported is marked as inactive in the accounting system, it will give you a message saying an Element used in the file has been marked as inactive, and the import function will be terminated.
Operating Instructions
The Timesheet Import supplement allows you to take spreadsheet files or a file formed in time clock software and form a timesheet batch. The files must be set up in a standard format.
Things You Should Know
- If you are importing timesheets for annual salary employees who have zero hours in the pay period field of class setup (semi-monthly and monthly payrolls), the total hours in the pay period will show on the batch control and timesheet batch report.
- Care needs to be taken in how the spreadsheet files to be imported are named when saved. If the files are named using the employee code at the beginning of the file name, followed by an alpha character, other character or space, each imported document will be automatically attached to the appropriate employee timesheet. If they are named in any other way, the imported files will be attached to the batch but not to the individual timesheet.
File Format
- Beginning at cell A1 and with each row representing one line of distribution.
Column A: Employee Number
Column B: Program Element
Column C: Pay Code
Column D: Total Hours
Column E: (Optional) Dollar amount assigned if using Timesheet Adjustment
Column F: Local Tax Code
- The individual spreadsheet files or the time clock file must be Excel, Txt or CSV.
- If you are importing timesheets for employees that have multiple pay rates the system will display these employees and their available rates. You will then select the appropriate rate of pay for each pay code. To trigger the multiple rate option, make sure you use a pay code in the timesheet for that line of distribution that does not exist in the accounting system. An example might be MR (for multiple rate). Should an employee have two different additional rates, you need to use two different pay codes, such as MR and MP.
- If you import a file using an element code assigned to a program but use pay code LP, the system will change the element code to what is assigned to LP (Leave Without Pay)
- If you import a file using pay code LP but have a dollar amount in column E for that line of distribution, the software will change the dollar amount to zero.
- If there are no hours filled in for an element, when importing it will not import a zero record.
- Each timesheet must be saved as a separate file with only 1 sheet.
- Should you import a file containing an inactive element, the process will be stopped and a list provided of the inactive codes.
- Column F is used for splitting local wages and taxes. If an employee has multiple Local Tax Codes, make sure all the lines for that employee have the appropriate local code, otherwise those wages will not be used in the local tax calculation
NOTE: When importing a file containing leave for a leave type that has been set up in Payroll\Set Up\Leave Balance Type, the system will do an available leave balance check. If the amount of leave being imported exceeds the employee’s current leave balance plus hours earned during the current pay period, a message will be displayed "Employee's leave taken hours will exceed the total leave balance. Do you want to override?"
When developing the timesheet batch, the software is performing the following checks, and prepares a list of items that were found that can be displayed or printed. If codes from the file are not found in the database, it will stop the process of importing and give you a list of items found. These will need to be corrected in the original file.
- An employee code is found in the first field of the file but does not exist in the database.
- An employee code is found in the database, but it is a terminated employee.
- A program element code is found in the second field of the file that does not exist in the database.
- A program element code is found that is terminated.
- If you use Supplement #521 Terminate Elements, it will check to see if any program element codes found in the second field of the file have been terminated. It will give you the option to override.
- A pay code other than RT, OT, CE or CT is found in the third field of the file for a non-leave element code.
- If a pay code for a leave element that exists in the pay code set up table has been used that is not the same as in the pay code set up table, it will correct the pay code. For example, if you have 995100 AL set up in the database, but the file you are importing contains 995100 AK, the software will check the setup and change the AK to AL.
- An employee code is found with leave taken but no leave has been set up in the employee file.
- The software will perform leave balance checks, comparing leave taken amounts entered to the leave balances in the employee master file. If the amount taken exceeds the balance, it will give the warning and give you the option to print a list of employees whose leave taken exceeded their balance.
- When the timesheets are imported, the payroll amounts will be rounded to two decimals.
--If you are importing timesheets for employees that have a pay period (PP) rate in their employee file or "Ask for hours" is checked, it will stop and ask how many hours that employee worked for this pay period. Those employee’s hours should be noted prior to doing the import.
--When importing from Excel, the 300th record must not be blank and the file cannot exceed 500 records.
--If an Element in the file to be imported is marked as inactive in the accounting system, it will give you a message saying an Element used in the file has been marked as inactive, and the import function will be terminated.
Operating Instructions
- File Format - Choose either Excel, Text, or CSV.
- Import From - Select the drive and path where the file(s) you wish to import exists. You may select multiple files, or use shift click to select a range of files.
- Pay Period - Enter the beginning and ending dates of the pay period by clicking the combo box arrow. You may then type in the appropriate date or select the dates using the calendar function. Also, be sure to put a checkmark in the boxes in from and to.
- Posting Period - Select the posting period for this batch.
- Batch Description - You can enter a description for the batch that will print on the batch control, batch register and Batch Analysis.
- Attach Documents – Click on this if you wish to automatically attach documents.
- Import - After successfully importing, a message will be displayed telling you that the timesheet batch has been imported and you will be taken to the newly created batch. You need to check the batch register to make sure the data has been imported correctly.