Start a conversation

Timesheet Import from Excel

From version 1.00.17.100 (or later) there is a new facility to import timesheet information from an Excel spreadsheet.
The spreadsheet should contain specific information relating to the Site, candidates, Hours worked at each rate and Payroll Ref. No.

When the import process is run, it will analyse the spreadsheet to check the format, and will then allow the user to import the timesheet information into the system as a 'preview'. Any errors will be reported at that point to allow the issues to be resolved. The displayed details can then be imported as timesheets using the [OK] button.

Note: The current version of the import program will only work with bookings which are made via a REQUIREMENT in the Influence system.
Bookings made Ad Hoc (e.g. via the Planner) or that are part of a Temp Vacancy cannot currently have timesheets imported in this way. This restriction may be addressed in future revisions to the software.

 

Spreadsheet format

In order to import the details into the system the spreadsheet must be in a particular format; an example of a valid format is shown below.

Cell B1: Should contain the NAME of the company the timesheet information relates to. (It will be shown during the import process and is for reference only.
Cell B2: Contains the 'week commencing' date for these timesheet records. It should match the working week in your Influence system, e.g. If your week is Mon-Sun, then the date should relate to a Monday. It should be in the format dd/mm/yyyy

Row 3, Column 'C' Onwards: This should contain headings relating to the Pay Rate for which the hours are to be imported.
Valid Headings are Basic, Rate 1, Rate 2, Rate 3, etc....Rate 9
Hours recorded under 'Basic' will be imported into the "basic" rate.
Rate2 - will import against the relevant rate in your Influence system.

(Tip: To see which rate is which in Influence go to Maintenance > Setup > Code Tables, and look at code table Pay Code [154] Tick the Extra Info [  ]
check box and look at the 'Slot' to see which rate is which.
)

Row 4, Column A: Should always contain the word NAME

Row 4, Column B: Should always contain the word PAYREF

Row 4, Column C onwards: These columns can contain the DAY names (Mon/Tue/Wed/Thur/Fri/Sat/Sun) or the word Any.
If they are set to Any then the hours are simply  spread evenly across the existing bookings that week. 

(NOTE: Day names *can* be the full name, e.g. Monday, Tuesday, etc. Other variations such as Mon, Tues,  Wed, are also valid.)

Row 5 onwards: From row 5 down, you should record the actual timesheet data that will be imported.
Candidate names should be recorded under the NAME column, Payroll Reference No's under the PAYREF column and then the relevant number of hours under the appropriate column for Rate/Day.

EXAMPLE IMPORT SHEET.


(In the example above hours will be imported at BASIC rate for Mon-Fri, Saturday will be imported using Rate3, and hours at Rate 2 (Overtime) will be spread evenly across the week to make up the total)



How it Works

In order to be able to import timesheets, bookings must have been created in Influence before you attempt to import the timesheet information. If there are no bookings at the chosen site for the specified week, then the import process will inform you that no bookings exist.

Go to

 Maintenance > Tools > General TS Import


Once the screen opens you may drag/drop your MS Excel spreadsheet onto the field labelled Spreadsheet
You must then use the field labelled Company at the top of the screen to select the company in the database for whom you wish to import timesheets. You may then choose a Branch (if the company has multiple branches)

Press the [Load] button to scan the spreadsheet. The system will then scan the sheet looking the the information and display a summary of what has been found in the grid below.

W/C: This will show the date found in the spreadsheet and will indicate the date from which the system is looking for bookings to timesheet against.

Company: This will display the company name from cell B1 of the spreadsheet and is purely a visual check to help make sure that you are importing the right spreadsheet information into the correct company. (i.e. The company you chose at the top of the screen.)

Name: This will display the NAME of the candidate as found/displayed on the spreadsheet.

Payroll ID: Displays the Payroll Reference No of the displayed/found candidate.

Reference: This is the reference No. of the candidate in the Influence database.

Role: This is a the 'main' role for the candidate (i.e. the one seen at the top-right of their candidate record in Influence)

Rate Name:  Will display the number of hours imported for that Day/Rate combination.
Mon/Tue/Wed/Thu/Fri/Sat/Sun <Or> Any

Errors: If there is a problem which would prevent the import, the system will give details of the errors here.
Examples include "No Matching Bookings Found", "Timesheeted Bookings Found" or "Candidate has no Payroll ID"

If there is more than one error for an individual worker you can see both errors by hovering the cursor over the error message.

If there are any errors, you can correct them in the system using another session, then come back to this screen and press the
[Re-scan] button to re-read the spreadsheet and ensure the errors have been dealt with.

Checking and Importing

The system displays a summary of the hours it has found in the spreadsheet using the HOURS box at the top right.
We recommend that you check this against the totals on the source spreadsheet (i.e. Open the sheet in Excel and TOTAL the columns to make sure it all agrees)

If this agrees with the total from your spreadsheet then pressing [OK] will import these hours into the system, allocate a timesheet number and mark the records as timesheeted within the Influence database.

 

 

Troubleshooting

Below we have listed some of the error messages along with advice on how to handle these.

Timesheeted Booking Found: This indicates that some or all of the days in the import data have already had timesheet information entered into Influence. You should go to the Timesheet Entry screen within Influence and find these bookings. You can then review the information and either leave them as they are or remove the Timesheet number (if you have priority to do so) before attempting to re-import the data.

Unable to Find bookings for Worker with this Name: This indicates that the system has been unable to find any bookings at the chosen site for the worker name listed during the week indicated in the import spreadsheet. In this case you should check that the name on the spreadsheet and the name in Influence match exactly,
e.g. Bill Smith and Billy Smith will not match, and would create such an error.

Unable to find bookings for worker with this Payroll ID: Similar to above, there are no bookings at the selected site for a worker who has the Payroll ID that matches the PayRef taken from the import spreadsheet. (Note: If a PayRef exists on the spreadsheet the system will always use this to try to find a worker in preference to their name.)

Worker has no Payroll ID: In this case the system has found booking based on the NAME of the worker in the import, but they do not have a Payroll ID in Influence. To resolve this go into the worker record, choose the 'Finance' page of their record and ensure that they have been given a Payroll No. Once done Re-Scan the sheet to import the data.

No Relevant Pay Rates Found: This indicates that there are no Pay Rates setup for this ROLE at either the site, the company, the Requirement or overall in the system. when importing timesheets the system insists that you must have a relevant RATE setup for this worker/Role/Site/Shift combination. To fix the issue go to either the requirement or company and add a suitable rate, then re-scan to import the data.

 

 

 

Choose files or drag and drop files
Helpful?
Yes
No
  1. Graeme Orchard

  2. Posted
  3. Updated

Comments