Importing Timesheets

Using the Electronic Timesheet Import feature, the user can set up the accounting system to receive data from an external timecard device, such as an electronic time clock, a Microsoft Excel spreadsheet, etc. This involves entering the name of the device from which data will be used, defining the format of the data to be imported, and optionally creating a Distribution Code Translation Table. If a Distribution Code Table will be used, it will be necessary to know the meaning of each data field from the external timecard device so that they will be assigned correctly to General Ledger accounts.

The box on the left contains a list of all the different timesheet import formats currently set up. The system will always automatically add a source titled "Timesheets Exported from accounting". This will import timesheets that have exported directly from the Timesheet Browse Screen. On the right is a list box that will show a preview of the timesheet line items to be imported. It will be filled when the Preview button is pressed.

By pressing the Delete button, all information previously for the highlighted source will be completely erased and will have to be reentered if needed.

When the Preview button is pressed, the system will use the highlighted source information to read the text file and display the information to be imported in the list box above.

Once the information has been read and is being displayed in the list box, pressing the Import button will convert the displayed information into unposted timesheet files.

To Add a new source, press the Add button. To change a previously setup source, highlight the desired source and press the Change button. When the Add or Change button is pressed, the following screen will come into view.

This screen shows all the information that must be entered in order to properly setup an external timesheet device.

Enter the name of the device. Either create a descriptive name or enter the actual name of the device itself.

The external timesheet device will probably store its data on a disk. This information will be formatted as either a Comma Delimited File, Tab Delimited File, or a Fixed Position File. The accounting system MUST know which type of format is used. This is very important for the system to read the data correctly!

A Comma Delimited ASCII File will have commas separating the fields containing information. It will appear as follows:

A Tab Delimited ASCII File will have tabs separating the information fields. It will appear as follows:

A Fixed Position ASCII File will have spaces between the information fields. It will appear as follows:

Press the Add button to add a new field to the Interface. The following screen will come into view.

At a bare minimum, each record in the file must include the Employee Name or Employee ID (Internal or External), the number of hours worked (either in the Hours field or in Regular, Overtime and Premium Hours fields), and some form of cost distribution (either a GL Account, a Job/Cost Code combination, an Equipment/Cost Category combination, or a recognized Cost Distribution code).

	Employee ID	Must match Employee ID in A-Systems Software
	External Employee ID	Must match External Employee ID in A-Systems Software
	Employee Name	Employee Full Name. This field is unnecessary if a valid Employee ID is imported
	Employee First Name	This field is unnecessary if a valid Employee ID is imported
	Employee Last Name	This field is unnecessary if a valid Employee ID is imported
	Date	Date the work was performed
	Hours	Total number of hours worked
	Rate Type	‘R'egular, ‘O'vertime, or ‘P'remium. This field is used if the Hours field is used
	Regular Hours	Number of regular hours worked
	Overtime Hours	Number of overtime hours worked
	Premium Hours	Number of premium hours worked
	Regular Rate	Hourly rate for regular hours worked
	Overtime Rate	Hourly rate for overtime hours worked
	Premium Rate	Hourly rate for premium hours worked
	Regular Earnings	Amount earned based on regular hours
	Overtime Earnings	Amount earned based on overtime hours
	Premium Earnings	Amount earned based on premium hours
	Salary Amount	If a value is found in this field, the Timesheet Type will be automatically set to 6-Salary
	Other Pay Amount	If a value is found in this field, the Timesheet Type will be automatically set to 3-Taxable Other Pay
	Reported Tips	Amount of tips kept by employee and reported to employer
	Retained Tips	Amount of tips collected by employer to be paid to employee
	Start Time	Time of day work was started
	Finish Time	Time of day work was completed
	Start Time 2	Time of day work was started
	Finish Time 2	Time of day work was completed
	*Cost Distribution	If your software is not able to provide cost detail (GL Account or Job ID and Cost Code) to match A-Systems software, this is a code that will be decoded by the user when setting up the import table.
	*GL Account	General Ledger Account Number (must match a valid A-Systems code)
	*Job ID	Job ID (must match a valid A-Systems code)
	*Cost Code	Job Cost Code/Type (must match a valid A-Systems code)
	*Equipment ID	Equipment ID (must match a valid A-Systems code)
	*Cost Category	Equipment Cost Category (must match a valid A-Systems code)
	Description	Description of the line item detail
	Timesheet Type	1=Hourly, 3=Taxable Other Pay, 4=Non-taxable Other Pay, 5=Deduction, 6=Salary (Value defaults to “1” if omitted.)
	Deduction Number	Deduction number (for type 5 timesheets) (must match a valid A-Systems Deduction number)
	Certified Craft	(must match a valid A-Systems code) (Value pulled from employee record if omitted)
	Workers Comp Craft Code	(must match a valid A-Systems code) (Value pulled from employee record if omitted)
	Workers Comp State	(must match a valid A-Systems code) (Value pulled from employee record if omitted)
	General Liability Craft Code	(must match a valid A-Systems code) (Value pulled from employee record if omitted)
	General Liability State	(must match a valid A-Systems code) (Value pulled from employee record if omitted)
	Tax State	(must match a valid A-Systems code) (Value pulled from employee record if omitted)
	Unemployment State	(must match a valid A-Systems code) (Value pulled from employee record if omitted)
	Locality 1	(must match a valid A-Systems code) (Value pulled from employee record if omitted)
	Locality 2	(must match a valid A-Systems code) (Value pulled from employee record if omitted)
	Fringe Table	(must match a valid A-Systems code) (Value pulled from employee record if omitted)
	Fed Withholding Amount	If an amount is entered here, the system will withhold exactly this amount of federal tax for this timesheet
	State Withholding Amount	If an amount is entered here, the system will withhold exactly this amount of state tax for this timesheet
	Other Pay ID	If a matching Other Pay Type is found, the attributes of that pay type will be used for this timesheet
	Equipment Used	The Equipment ID of the piece of equipment operated by this employee. This will create an Equipment Usage Log in addition to the timesheet

If the text file being imported is composed of Fixed Position fields, enter the number of characters in this field.

This field contains the data path of the text file on the disk. This tells the accounting system where to find the data to be imported. The type of file, Comma Delimited ASCII or Fixed Position ASCII, is not important at this time. Simply type the data path or use the lookup button to find the file on the disk. For example: in the above screen, the accounting system will look for the file called 'Export.txt' in the My Documents folder on the hard drive.

Use this field to select the format of the date. Use the down arrow to select from a list of formats already in the accounting system. In the screen above, the format is DD/MM/YY. November 10, 2004 will look like 10/11/04.

Sometimes the numeric fields in an exported data file will have numbers listed without decimals. For example, 15 hours might be represented as 1500 or $22.58 might be represented as 2258. In both these examples, the user should set the decimal shift to 2 places. When the numbers are imported, the system will adjust them and put the decimal in the correct place.

If this box is checked, the system will keep track of how many hours are in the import file for each employee for each day. Once an employee has more than 8 hours in any given day, the additional hours will be changed to overtime hours.

If this box is checked, the system will keep track of how many hours are in the import file for each employee for each week. Once an employee has more than 40 hours in any given week, the additional hours will be changed to overtime hours.

If this box is checked, the system will import time in 15 minute increments. This is especially helpful if Start Time and Finish Time are being used.

If this box is checked, the system will issue a warning if an imported timesheet will cause the cost to-date on the assigned cost code to go over budget.

If this box is checked, the system will issue a warning if an imported Time Off timesheet (Vacation, Sick Pay, etc.) will cause the employee to use more than the number of hours available for that time off type.

On the left side of the table is the Import Code (i.e. the number for the Cost Distribution imported from the data from the external timesheet device). On the right side of the table is the accounting system Code. This is how the system knows to which General Ledger accounts to apply the employee's time.

Highlight the code(s) to be deleted, and click the Delete button.
Warning: The code(s) will then be completely erased. There is no Undo for this!

When ALL information has been entered, press the OK button to save the information or press Cancel to return the previous screen. By pressing the Cancel button, no information will be saved.

This is the most important step in setting up the Employee Timesheet Interface. ALL Import Codes must have a Code translation. They should be entered NOW so as not to be forgotten. If these codes are not setup correctly, there will be major problems later. Please double check work when entering these codes.

Enter the numerical code for the Cost Distribution from the external timesheet device. In the examples above, the code was 1321649.

Click on the down arrow key to select the 'detail type' for the accounting system code. The list of options will be G/L Account (General Ledger account), Job and Equipment.

Enter the General Ledger account to which the numerical code corresponds for the Cost Distribution from the external timesheet device. Press the lookup button to select from the entire list of General Ledger accounts. For example: suppose the numerical code, or 'Import Code', is 1345769 and that it is used for salaries of clerical people. The General Ledger account for the salaries of clerical people is 62400. The account number 62400 would then be entered.

To add another General Ledger account or to add another 'detail type' , press the OK button and the data fields will become blank again. Press OK after every entry to save the information. When finished, press the Cancel button to return to the previous screen.

Enter the Job ID number and Cost Code for the job to which the numerical code corresponds for the Cost Distribution from the external timesheet device. Press the lookup buttons to select from the entire list of jobs and cost codes for the company. For example: suppose the numerical code, or 'Import Code', is 1321649 and that it is used for labor of setting concrete at the site of Job 100. '100' would be entered for the Job ID and 00302L would be entered as the Cost Code for 'site-concrete'.

To add another Job or to add another 'detail type', press the OK button and the data fields will become blank again. Press OK after every entry to save the information. When finished, press Cancel to return to the previous screen.

Enter the Equipment ID number and Cost Category for the job to which the numerical code corresponds for the Cost Distribution from the external timesheet device. Use the lookup buttons to select from the entire list of equipment and cost categories for the company. For example: suppose the numerical code, or 'Import Code', is 2384167 and that it is used for the company truck to haul wire to the site. 'TR-1' would be entered for the truck ID and 'HAUL' for cost category.

To add another Equipment code or to add another 'detail type', press OK and the data fields will become blank again. Press OK after every entry to save the information. When finished, press Cancel to return to the previous screen.

Remember: ALL Import Codes must have a Code translation or the Electronic Timesheet Interface will NOT work properly. The information will come out incorrectly, and there will be problems!

To import the timesheets from the external file, click the Preview button. The file will be interpreted and the list box on the right will be filled as shown in the following window.

The timesheets are displayed and can be previewed in this window before actually importing them into a batch.

To create the batch of timesheets, press the Import button. The program will perform all the functions associated with timesheet entry by hand. Here are a few examples. Employee wage rates will be looked up from the employee file and possibly overridden by prevailing wage rates. The system will check for entries that will cause job costs to go over budget and display an information window if this is the case. The system will warn if an employee is taking paid time off hours that are more than the hours he/she has accrued.