Employee Data Take On Details

Owned by Mark Holland (Unlicensed)
Last updated: Apr 21, 2020
7 min read

Introduction

The file (current version EmployeeDataTakeOn.xltm) should be opened from a link rather than from within excel. Once open the Data Input worksheet should be selected and the fields entered as described below. Mandatory columns are marked below with an (M) and have a Bold header in the file. Once completed the Control worksheet tab should be selected and the Validate Data button clicked.
 
If errors are found the user will be asked whether they wish to correct the errors prior to export (at this point yes should be clicked unless sure that the validation can be ignored).

On the Data Input sheets any error will  be highlighted in red in the right hand corner of the field, hovering over this will give details of the error.

Once corrected the Validate Data button should be re-clicked. The user will get the message that 3 separate csv files will be created and is asked to specify the location where the files will be created. The 3 files created are EmployeeStarter.csv, EmployeeYTDNIBalances.csv and EmployeeYTDPayBalances.csv. In the case  of  the two balances files these will be empty unless this is a mid-year migration where YTD values are being imported (if empty they do not require import).


The created files should be imported via the Import/Export -> Import Data screen. Selecting the relevant matching import format

File Details

Key Details

A: Employee Number

  • Enter if already allocated.
  • If a mid-year migration, this needs to match the reference number for the employee previously submitted via the FPS.
  • If left blank, System Generated Employee number must be selected through the  Company -> General screen.

B: Employee Forename (M)

C: Employee Surname (M)

D: Employee National Insurance Number

E: Payroll Name (M)

  • Weekly
  • Monthly
  •  Quarterly,
  • Annual.

Payroll Names outside of this are allowed in InPay.  For example a Company may have multiple weekly payrolls named Weekly1, Weekly2 and Weekly3. Therefore the validation on this field is only a guide and can be ignored where necessary. Payroll Name should match the Payroll Number field  on the Payrolls -> Payroll page in InPay.

F: Sex (M)

  • M/F

G: Date of Joining Company (M)

  • This is used as both the Date of Joining and Service Start Date. If the Service Start Date is different because of TUPE rules, the date should be edited in the Starter file created by this macro

H: Date of Birth (M)

I: Title (M)

  • Mr/Mrs/Miss/Ms/Dr/Rev/Rt Hon/Sir.
  • Further titles can be added through the Company -> Company Lookup screen.
  • The file will only validate against the list (Mr/Mrs/Miss/Ms/Dr/Rev/Rt Hon/Sir). If additional titles have been added to InPay the validation can be ignored.   

J: Employee Leave Date

  • Leavers in previous financial years should be excluded from the file.

K-P: Employee Address

  • If populated a minimum of Address1, Town and Post Code should be entered

Q:  Method of Payment (M)

  • BACS/Cash/Cheque/Auto Pay

R: Tax Code

  • Not required if the worker is a starter (details will be obtained from Start Statement or P45)

S:  Tax Basis

  • Required if Tax Code is entered. 0 = Cumulative, 1 = Wk1\Month 1 Basis

NI Details

T:  Employee National Insurance Table Letter (M)

  • A/B/C/H/J/M/X/Z – This is the national Insurance category Letter, not the last letter of the NI number.

U: Director Start Date

  • If Director

V: Director Non-Cumulative status for calculation of NIC

  • If Director

Previous Employment

W: Previous Taxable Pay

  • Supplied via HMRC P6 or P45
  • Is only required and will only be used in calculation if Date of Joining is in current tax year

X:  Previous Tax Paid

  • Supplied via HMRC P6 or P45
  • Will only be used in calculation if Date of Joining is in current tax year

Starter Statement

Y: Statement (M - If employee is a starter) 

  • A/B/C

Z: Student Loan

  • Should only be set if Statement entered
  • Will default to N

AA: Student Loan Plan Type

  • 1 or 2
  • Should be set only if Student Loan is set to Y and Student Loan Repay Directly is set to N or Migrated is set to Y and worker has a student loan

AB: Student Loan Repay Directly – Y/N

  • Should only be set if column Student Loan is set to Y
  • Will default to N

AC:  Student Loan before last 6th April – Y/N

  • Should only be set if column Student Loan is set to Y
  • Will default to N

AD: PG Loan Not Repaid

  • Should be set if worker has current Postgraduate Loan

AE: PG Monthly Payments – Y/N

  • Should only be set if column AD is set to Y and a Starter Statement is entered
  • Will default to N

AF:  PG before last 6th April – Y/N

  • Should only be set if column AD is set to Y and a Starter Statement is entered
  • Will default to N

Location

AG: Department

  • Must match Description in Company -> Company Lookup screen
  • If the worker has multiple Departments the Departments must be included in the Department 1, Department 2 or Department 3 field and will be used as the home department.

AH: Division

  • Must match Description in Company -> Company Lookup screen

AI: Cost Centre

  • Must match Description in Company -> Company Lookup screen
  • Will populate the Cost Centre with 100% allocation. If the worker has a more complicated costing it can be entered directly in the Starter csv file created by the macro

AJ: Department 1

  • Must match Description in Company -> Company Lookup screen
  • Only required if worker is split between multiple departments.

AK: Department Percent 1

  • Required if Department 1 is entered.
  • Percent 1, 2 and 3 field must add up to 100

AL: Department 2

  • Must match Description in Company -> Company Lookup screen
  • Only required if worker is split between multiple departments.

AM: Department Percent 2

  • Required if Department 2 is entered.
  • Percent 1, 2 and 3 field must add up to 100

AN: Department 3

  • Must match Description in Company -> Company Lookup screen
  • Only required if worker is split between multiple departments

AO: Department Percent 3

  • Required if Department 3 is entered.
  • Percent 1, 2 and 3 field must add up to 100.

Payments

AP: PayElement1

  • Will create a permanent payment against the worker for this Pay Element. This should match the Pay Element field in the Company -> Pay Elements screen and be of type payment.

AQ: PeriodAmount1

  • If PayElement1 is populated then either this field or AnnualAmount1 must also be populated (fields are mutually exclusive). If populated should contain the value that is paid to the worker each pay period.

AR:  AnnualAmount1

  • If PayElement1 is populated then either this field or PeriodAmount1 must also be populated (fields are mutually exclusive). If populated should contain the value that is paid to the worker annually.

AS:  PayElement2 (See AP)

AT: PeriodAmount2 (See AQ)

AU: AnnualAmount2 (See AR)

AV: PayElement3 (See AP)

AW: PeriodAmount3 (See AQ)

AX: AnnualAmount3 (See AR)

Deductions

AY: Deduction1

  • Will create a permanent deduction against the worker for this Pay Element. This should match the Pay Element field in the Company -> Pay Elements screen and be of type deduction.

AZ:  PeriodAmountD1

  • If Deduction1 is populated then either this field or AnnualAmountD1 must also be populated (fields are mutually exclusive). If populated should contain the value that is deducted from the worker each pay period

BA: AnnualAmountD1

  • If PayElement1 is populated then either this field or PeriodAmount1 must also be populated (fields are mutually exclusive). If populated should contain the value that is deducted from the worker annually.

BB: LoanBalance1

  • If the deduction is for the purpose of paying back a loan to the employer the balance remaining should be entered here. InPay will cease the deduction when loan balance has been paid

BC: Deduction2 (See AY)

BD: PeriodAmountD2 (See AZ)

BE: AnnualAmountD2. (See BA)

BF: LoanBalance2 (See BB)

Bank Details

BG: Bank Sort code

  • 6 numeric digits, less than 6 will be padded with zeros. Must be populated if BH or BI is populated

BH: Bank Account Number

  • 8 numeric digits, less than 8 will be padded with zeros. Must be populated if BG or BI is populated

BI: Account Name

  • Maximum 50 Characters. Must be populated if BG or BH is populated

BJ: Roll / Building society reference

  • if applicable

Pension Scheme

BK: Pension Scheme Name / description to show on Pay advice

  • Must match description field on Company -> Pension Schemes screen

BL:  Employee Pension Value or Percentage

  • Numeric
  • Blank if Basic Scheme is ticked on the Pension Schemes page as this uses legislative data

BM:  Employee Pension indicator Value or Percentage

  • V or P
  • Blank if Basic Scheme is ticked on the Pension Schemes page as this uses legislative data

BN: Employer Pension Valve or Percentage

  • Numeric
  • Blank if Basic Scheme is ticked on the Pension Schemes page as this uses legislative data 

BO:  Employer Pension indicator Value or Percentage – V/P

  • V or P
  • Blank if Basic Scheme is ticked on the Pension Schemes page as this uses legislative data

National Insurance Breakdown

BP to BU should only be entered for mid-year migrations. The figures are used as the opening balances for the first period that is calculated by the InPay software. The figures will be entered against the current NI Letter. On the rare occasion the worker has changed NI Letter during the tax year changes should be manually made against the worker in the Employee → YTD NI Balances screen.

BP: To Lower Earnings Limit

BQ: Lower Earnings Limit to Primary Threshold

BR: Primary Threshold to Upper Earnings Limit

BS: Niable Pay this Employment

BT: Employees National Insurance Contributions

BU: Employers National Insurance Contributions

Year to Date Values

BM to BR should only be entered for mid-year migrations. The figures are used as the opening balances for the first period that is calculated by the InPay software.

BV: Taxable Pay this Employment

  • Excluding SMP and SPPb

BX: Tax paid this Employment

BY: Student Loan Paid this Employment

BZ: Postgraduate Loan Paid this Employment

CA: SMP Paid This Employment

CB: SPPb Paid This Employment

RTI Compliance

CC: Irregular Payment Indicator

  • Y = Paid on irregular basis
  • N = Paid every pay period

CD:  Hours Worked:

  • A = 0-15.99 hours per week
  • B = 16 – 23.99 hours per week
  • C = 24  – 29.99 hours per week
  • D = 30+ hours per week
  • E = Other

Miscellaneous

CE:  Contracted Hours -

  • Weekly contracted hours (will be used in rate and occupational absence calculations if applicable)

CF  Pay Type (will pick up company default selected in Company → General screen if not entered) 

  • Salaried
  • Grade
  • Hourly

CG: Work Email – This is required for Integrations with InTime and if the worker requires access to the payslip portal

CH: Migrated

  • Y/N – For mid-year migrations from another payroll Y would be entered here,  the employee will be import as “current” status and not a starter, in which case starter declarations are not required.

CI: Off Payroll Worker

  • Y/N - If left blank, the worker's off payroll status will be set to the default off payroll status for the entered payroll.

CJ: RTI Employee Number

  • The unique employee reference which is included in submissions to the HMRC.

Employee Spinal Point (Academies)

CK1:  Grade1

  • Must match a Grade showing through the Company -> Grade Screen.

CL1:  Point1

  • Must match a Point ID showing through the Company -> Grade Screen for the Grade entered in BW1

CM1:  FTE1

  • 0 -100 

CN1:  Full Time Hours1

  • Weekly Hours 

CO1:  Working Hours1

  • Weekly Hours 

CP1: Working Weeks1

CQ1: FTE Working Weeks1

CR1: Academy Pay Element1

  • This should match the Pay Element field in the Company -> Pay Elements screen, and the element must be of type payment.

CS2: Grade2 (See CK1)

CT2:  Point2 (See CL1)

CU2: FTE2 (See CM1)

CV2:  Full Time Hours2 (See CN1)

CW2: Working Hours2 (See CO1)

CX2: Working Weeks2 (See CP1)

CY2: FTE Working Weeks2 (See CQ1)

CZ2: Academy Pay Element2 (See CR1)

Miscellaneous 2

DA: OSP Scheme

  • The name of the occupational sickness scheme the worker is to be added to, as specified by the scheme name on the Company → OSP page

DB: Schedule Override

  • Override the worker's schedule must be whole weeks Sunday - Saturday (example 1 week on 1 week off NYYYYYNNNNNNNN)

DC: Schedule Start Date

  • If the schedule is for multiple weeks it is possible that a date other than the employee's date of joining may be entered