Question 1

Smart Formula Allowances (from Xero)

Accepted Answer

Introduction

Smart Formulas can be used to create allowances in addition to determining the earnings rates for employees.

These allowances can be either automatically or manually applied depending on how they are set up.

Getting Started

The following steps must be completed to use Smart Formulas for allowances:

Set up Pay Items in Xero
Assign Pay Items to Employee Pay Templates in Xero
Enable Smart Formulas in UpSheets
Refresh Smart formulas from Xero to UpSheets
Import your file using Smart Formulas

This document explains Step 1. Set up Pay Items in Xero for Allowances. Click here for detailed instructions on steps 2-5

1. Set up Allowance Pay Items in Xero

The steps below are for loading smart formulas to set up automatic and manual allowances with Smart Fomulas.

Pay Items are set up in Xero by navigating to Settings> Payroll Settings> Pay Items

To be used for timesheets the pay items must:

Have a Rate Type = Multiple; or
Have a Rate Type = Rate per unit and Type of units = hours
Have a Rate Type = Rate per unit and Type of units = The same time unit used for the Ordinary Earnings Type of the Employee (Days/Weeks etc)

To enable a pay item for use as an allowance Smart Formula, the name must use the following format:

NUMBER NAME [DAY][PERIOD][TYPE][MINMAX]

Example:

001 Daily Allowance [WEEKDAY][10~24][DAY]

For more information on defining NAME, NUMBER, DAY please see Smart Formulas (from Xero)

Period

The Period element can be used to define the minimum and maximum hours for applying the allowance, e.g.

[8~24] will apply the allowance only if 8 or more hours are worked.

[0~24] or [] will apply the allowance if any hours are worked.

This can be used to create different allowances for different periods e.g.

[0~10] Will be applied if the hours are less than 10

[10~24] Will be applied if the hours are 10-24

For allowances that require a time range, this can be defined as below and also limited by using the MINMAX element. To do so use the start_time - end_time option. Each time must be set using four digits and 24 hours clock.

Examples:

[0000-1300] = midnight to 1pm

[1400-1600] = 2pm to 4pm

[2000-0000] = 8pm to midnight

MinMax

The MinMax defines the number of hours when a time range is used for the period (e.g. [0000-0600].

The format is:

Examples:

[0~5] = the first 5 hours

[5~24] = From 5 to 24 hours

[0~24] = the first 24 hours

This element is only available for certain allowance types as defined in this guide.

Type

Determines the type of allowance. The code value below should be entered into the formula to define the type of allowance.

Time-Based Allowances

Other Allowances

Examples

Time-Based Allowances (HOUR / SHIFT / DAY / WEEK)

All of these allowances work in a similar manner and can be used with the DAY/PERIOD elements to determine when they are applied.

The main difference is how they are applied to imported lines:

HOUR - Applies the allowance to every hour worked in the period
SHIFT - Applies the allowance to every shift (i.e. once for every timesheet line imported even on the same day)
DAY - Applies the allowance once per day (i.e. once even if multiple shifts are imported on the same day)
WEEK - Applies the allowance once per week (i.e. once per week so long as a line is imported during that week). The DAY element is not used for this type)

001 Allowance [WEEKDAY][0000-0600][HOUR]

Will apply the allowance for every hour worked between midnight and 6am on a Monday, Tuesday, Wednesday, Thursday or Friday. This can be used to add an additional hourly allowance to a base rate. If an emplyee worked from 02:00 - 03:30 they would be allocated 1.5 x this allowance. This allowance is only configured to work for periods between set hours.

001 Allowance [WEEKDAY][1~24][SHIFT]

Will apply the allowance for every timesheet line imported for a Monday, Tuesday, Wednesday, Thursday or Friday and the employee has worked at least 1 hour (and less than 24). This could potentially apply multiple times in a single day.

002 Allowance [WEEKDAY][8~24][DAY]

Will apply the allowance if one or more timesheet lines are imported for Monday, Tuesday, Wednesday, Thursday or Friday and the total hours each day are between 8 and 24. This can only apply once per day, but could apply up to 5 times if each weekday is worked.

003 Allowance [WEEKEND][][DAY]

Will apply the allowance if one or more timesheet lines are imported for Saturday or Sunday. The number of hours is not a factor as it is left blank. This would apply 2 allowance lines if both days are worked.

004 Allowance [EVERY][40~168][WEEK]

Will apply the allowance if the total hours worked in a week is over 40 (but below 168). This would apply a maximum of once per week.

Broken/Split Shift (BS)

A Broken/Spilt Shift allowance will be applied when an employee works multiple shifts in a day that have a gap between them. The size of the gap between shifts is defined using the period element.

The fourth [MinMax] element can be used to determine the maximum number of allowances to be applied in a day or week. e.g. [1~3] is once per day and 3 per week. This is optional and if left out it will apply the allowance every time the condition is met.

Examples

001 Split Shift [EVERY][1~3][BS][1~7]

Will apply the allowance if the gap between two shifts is more than one hour but less than 3. This will be applied a maximum of once per day and 7 times per week.

002 Split Shift [EVERY][3~21][BS][2~3]

Will apply the allowance if the gap between two shifts is more than three hours but less than 21. This will be applied a maximum of twice per day and three times per week.

003 Split Shift [WEEKDAY][1~23][BS]

Will apply the allowance if the gap between two shifts worked on a weekday is more than one hour but less than 23. There is no limit to how many times this will be applied each day or week.

Minimum Engagement (ME)

A Minimum Engagement allowance is applied when a shift is less than a set number of hours. The number of units is automatically calculated as the difference between the hours worked and the set number of hours. In this way, it can be used to pay an employee a minimum number of hours even when fewer are worked.

The MINMAX element is used to determine the minimum and maximum hours where the allowance should be applied.

Examples

001 Minimum Engagement [EVERY][][ME][0~4]

Will apply the allowance if any shift is worked for less than 4 hours. The number of units applied will be equal to 4 less the number of hours worked. If 1 hour is worked, the allowance will be for 3 hours. If more than 4 hours are worked, no allowance is applied.

002 Minimum Engagement [EVERY][][ME][1~2]

Will apply the allowance if a shift is worked for more than 1, but less than 2 hours. The number of units applied will be equal to 2 less the number of hours worked. If 1.5 hours are worked, the allowance will be for 0.5 hours. If more than 2 hours or less than one hour are worked, no allowance is applied.

Top Up (TU)

A Top Up allowance applies one unit of the allowance whenever an employee works at a particular time.

Examples

001 Top Up [MON-FRI][1900-0000][TU]

Applies the allowance if a shift is worked that starts, finishes or encompasses the period of 7pm to midnight on a Monday - Friday. This is usually set up In Xero as a rate per unit allowance to pay a set amount as an extra payment for a shift.

Manual (MAN)

A Manual allowance is not applied automatically, but can be added in UpSheets after importing your file and then uploaded to Xero.

Please note, these allowances must be added to all employee pay templates in order for them to be applied.

Examples

100 Manual Allowance [MAN]

When assigned to an employee an Add Line link will be displayed within UpSheets:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/FMpop3v7tb/add_line.png (add_line)

Clicking the link will display all manual allowance to be selected and applied to the employee on that date:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/YGG357leyO/add_line_modal.png (add_line_modal)

Question 2

File Formats for Projects (All Countries)

Accepted Answer

UpSheets allows you to upload the following Payroll transactions to Xero:

Tasks (Create or update existing)
Time (Create only)

Selecting Options

After connecting to Xero, select the appropriate options on the Import CSV File screen:

Projects Options

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/ME2fL6xMMW/8.png (Projects Options)

Notes on file formats

Clicking the Download Sample button on the Import screen will download a file with your Xero data in it and is the easiest way to get started.
A header row is not required, but it will save you time as UpSheets matches the headings automatically where possible and saves you from selecting them individually.
The order of the columns in your file does not matter.
Extra columns will be ignored, but it is faster if they are removed.
You can combine multiple transaction types by including all fields in a single file (Download Sample).

Task File Format

(Download Sample)

Time File Format

(Download Sample)

You can combine multiple transaction types by including all fields in a single file (Download Sample).

For information on saving spreadsheets as CSV files see here

Question 3

Uploading files with multiple columns with hours and earnings rates

Accepted Answer

Background

In order to upload data to Xero using multiple earnings rates, the upload file must have a different line for each one. Sometimes the data extracted from another system will have a column for each type, rather than multiple rows.

We need data like this:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/mcdZFMTVhJ/row%20data.png (row data)

But data sometimes looks like this:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/Yqg5Yvad6Q/column%20data.png (column data)

Getting your data in the right format

There are a couple of options to get your data into a usable format:

Use Smart Formulas to calculate the earnings rates automatically.
Use Excel to transpose your data from columns into rows

Using Excel to transpose your Data

Download the Excel Template
Follow the steps on the Instructions tab
Adjust the data tabs and formulas to match your own data as required

If you need any help with getting your data into shape contact us for some help or advice.

Question 4

Overwriting Existing Xero Payslip Adjustments

Accepted Answer

Default Behaviour

The default behaviour when uploading payslip adjustments to Xero is to overwrite all existing adjustments on the payslip, so whatever you load will be all that exists for any given category (other than earnings created from timesheets and system generated lines such as tax).

For example, if you are uploading earnings, then any existing earnings will be overwritten, but the remainder of the payslip remains. So if you load 'Ordinary Hours' you will also overwrite any 'Overtime Hours' or 'Allowances' etc that aren't loaded via an approved timesheet.

For example - loading an Ordinary Hours line (10 Hours x $100)
Payslip Before

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/sfSLSr3HJ6/adj_before.png (adj_before)

Payslip After

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/jRfYQXq5A1/adj_default.png (adj_default)

Alternative Behaviour

It is possible to modify this in your user settings so that only the specific types you are uploading will be overwritten. To do so, select the option below and revalidate your file:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/AnA8dKFAip/Dont%20Overwrite%20Payslip%20Setting.png (Dont Overwrite Payslip Setting)

By changing this setting, if you load 'Ordinary Hours' you will only overwrite existing 'Ordinary Hours', not any 'Overtime Hours' or 'Allowances' etc (or any other categories such as reimbursements deductions etc).

For example - loading an Ordinary Hours line (10 Hours x $100)

Payslip Before

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/sfSLSr3HJ6/adj_before.png (adj_before)

Payslip After

Question 5

Defaulting the Rate for Payslip Adjustments from Xero

Accepted Answer

Setup

When validating paylsip adjustments and the rate field is not provided in the file, UpSheets can try to retrieve this from Xero if it is defined on a pay template for the employee.

This feature requires each payslip to be retrieved individually and will slow down the validation of your data (the more employees you have the more noticeable this will be). For this reason, the feature must be explicitly enabled in your user settings.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/mrmWFAZt1L/Pay%20Template%20Setting.png (Pay Template Setting)

The following errors relate to this type of upload

Rate is required if Rate Per Unit not set on the Pay Item in Xero

If Use Xero Pay Template Rates for Payslip Adjustments IS NOT enabled, this error is displayed when the rate is not provided in the file and it is also not set on the pay item itself. To resolve it, simply add the rate to the pay item, or the file and revalidate.

Rate is required if Rate Per Unit not set on the Pay Item or Pay Template in Xero

If Use Xero Pay Template Rates for Payslip Adjustments IS enabled, this error is displayed when the rate is not provided in the file and not set on the employee's pay template and it is also not set on the pay item itself. To resolve it, simply add the rate to the pay item, pay template or the file and revalidate.

Revalidating

If you have updated the pay template or payslip for an employee, you must select the option to refresh payslips when revalidating the file as shown below:

Question 6

File Formats for Timesheets, Leave and Adjustments (Australia)

Accepted Answer

UpSheets allows you to upload the following transactions to Xero:

Timesheets
Leave (Approved)
Adjustments (Payslips)
- Earnings
- Reimbursements
- Deductions
- Leave Accruals

You may include any/all of the supported transaction types in the same file. You just need to provide correct data in appropriate columns and select them when uploading. See File Formats for Combined Timesheets, Leave and Adjustments in a single file (Australia) for examples.

Selecting Options

After connecting to Xero, select the appropriate options on the Import CSV File screen:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/Cy2IwNTK8g/9.png (9.png)

Notes on file formats

Clicking the Download Sample button on the Import screen will download a file with your Xero data in it and is the easiest way to get started.
A header row is not required, but it will save you time as UpSheets matches the headings automatically where possible and saves you from selecting them individually.
The order of the columns in your file does not matter.
Extra columns will be ignored, but it is faster if they are removed.
You can combine multiple transaction types (Timesheets/Leave/Adjustments) by including all fields in a single file.

PLEASE NOTE - IN ALL OF THE EXAMPLES, YOU MAY USE A SINGLE 'FULL_NAME' FIELD INSTEAD OF 'FIRST_NAME/LAST_NAME'

Timesheets

(Download Sample)

Leave

(Download Sample)

Adjustments

(Download Sample)

Calculation Type, Rate and Hours for Adjustment Types

The fields values that required in an upload file depend on the type of adjustment being uploaded as described in the table below.

All adjustments require the same file format and ALL fields must be included in the file, even if left blank. This enables you to upload multiple adjustment types in a single file and process them at once.

For example, the Hours column is required even when loading a deduction or reimbursement but the value should be left blank as per the table below. Likewise, Earnings still require the Calculation Type column, even though the value would be left blank.

PLEASE NOTE - IN ALL OF THE EXAMPLES, YOU MAY USE A SINGLE 'FULL_NAME' FIELD INSTEAD OF 'FIRST_NAME/LAST_NAME'

You may optionally provide a Start Time and End Time column instead of a single Hours column

For information on saving spreadsheets as CSV files see here

Question 7

File Formats for Combined Timesheets, Leave and Adjustments in a single file (Australia)

Accepted Answer

UpSheets allows you to upload multiple different transaction types in a single file, so long as all of the required fields are included.

For more information on individual file formats see File Formats for Timesheets, Leave and Adjustments (Australia)

NOTE: All columns must be included in the file, even if their values are left blank. This is required to keep formats consistent and allow UpSheets to determine the type of transaction being uploaded automatically.

PLEASE NOTE - IN ALL OF THE EXAMPLES, YOU MAY USE A SINGLE 'FULL_NAME' FIELD INSTEAD OF 'FIRST_NAME/LAST_NAME'

PLEASE NOTE - IN ALL OF THE EXAMPLES, YOU MAY USE A SINGLE 'FULL_NAME' FIELD INSTEAD OF 'FIRST_NAME/LAST_NAME'

You may optionally provide a Start Time and End Time column instead of the Hours column

Question 8

Using Start Time and End Time instead of Hours

Accepted Answer

Most file formats require a column for Hours in order to upload data, but it is also possible to provide a Start Time and End Time column and have UpSheets calculate the hours automatically.

To do so, select the Enable Start/End Times option on the Import screen when importing your file:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/3hKtDQmL6g/start_end.png (start_end)

On the following screen make sure you select columns for Start Time and End Time. You may optionally also select a column for Hours* which will be used if the Start Time and End Time are not set.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/AdXfkcueFe/times.png (times)

If all fields are provided, the Start Time and End Time will be used instead of Hours

Question 9

File Formats for Combined Timesheets, Leave and Adjustments in a single file (NZ & UK)

Accepted Answer

UpSheets allows you to upload multiple different transaction types in a single file, so long as all of the required fields are included.

For more information on individual file formats see File Formats for Timesheets, Leave and Adjustments

NOTE: All columns must be included in the file, even if their values are left blank. This is required to keep formats consistent and allow UpSheets to determine the type of transaction being uploaded automatically.

PLEASE NOTE - IN ALL OF THE EXAMPLES, YOU MAY USE A SINGLE 'FULL_NAME' FIELD INSTEAD OF 'FIRST_NAME/LAST_NAME'

Timesheet, Leave and Adjustment file Format - Combined NZ ONLY

(Download Sample)

Timesheet and Adjustment file Format - Combined NZ ONLY

(Download Sample)

Timesheet and Leave file Format - Combined

(Download Sample)

Leave and Adjustment file Format - Combined NZ ONLY

(Download Sample)

PLEASE NOTE - IN ALL OF THE EXAMPLES, YOU MAY USE A SINGLE 'FULL_NAME' FIELD INSTEAD OF 'FIRST_NAME/LAST_NAME'

You may optionally provide a Start Time and End Time column instead of the Hours column

Question 10

File Formats for Timesheets, Leave and Adjustments (NZ & UK)

Accepted Answer

UpSheets allows you to upload the following transactions to Xero:

Timesheets
Leave (Approved)
Adjustments (Payslips) NZ Only - not Available in UK
- Earnings
- Reimbursements
- Deductions
- Leave Accruals

Please note Adjustments/Payslips are not available for United Kingdom Xero organisations

The file format for Timesheets, Leave and Deductions are provided below.

You may include any/all of the supported transaction types in the same file. You just need to provide correct data in appropriate columns and select them when uploading. See LINK File Formats for Combined Timesheets, Leave and Adjustments in a Single File (NZ & UK) for examples.

Selecting Options

After connecting to Xero, select the appropriate options on the Import CSV File screen:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/Cy2IwNTK8g/9.png (9.png)

Notes on file formats

Clicking the Download Sample button on the Import screen will download a file with your Xero data in it and is the easiest way to get started.
A header row is not required, but it will save you time as UpSheets matches the headings automatically where possible and saves you from selecting them individually.
The order of the columns in your file does not matter.
Extra columns will be ignored, but it is faster if they are removed.
You can combine multiple transaction types (Timesheets/Leave/Adjustments) by including all fields in a single file.

PLEASE NOTE - IN ALL OF THE EXAMPLES, YOU MAY USE A SINGLE 'FULL_NAME' FIELD INSTEAD OF 'FIRST_NAME/LAST_NAME'

Timesheets

(Download Sample)

Leave

(Download Sample)

Adjustments

(Download Sample)

Rate and Hours for Adjustment Types

The fields values that required in an upload file depend on the type of adjustment being uploaded as described in the table below.

All adjustments require the same file format and ALL fields must be included in the file, even if left blank. This enables you to upload multiple adjustment types in a single file and process them at once.

For example, the Hours column is required even when loading a deduction or reimbursement but the value should be left blank as per the table below. Likewise, Earnings still require the Calculation Type column, even though the value would be left blank.

PLEASE NOTE - IN ALL OF THE EXAMPLES, YOU MAY USE A SINGLE 'FULL_NAME' FIELD INSTEAD OF 'FIRST_NAME/LAST_NAME'

You may optionally provide a Start Time and End Time column instead of the Hours column

For information on saving spreadsheets as CSV files see here