Question 1

Using Public Holidays with Smart Formulas

Accepted Answer

Background

Smart Formulas can be used to select different earnings rates on public holidays. In order to do so, they must first be configured in UpSheets (even though Xero has public holidays configured, they are not available publicly).

Basic Setup

To use public holidays, it must first be enabled:

Smart Formulas> Smart Formula Settings> Holidays

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/MXaz7Onx3j/enable%20smart%20formulas.png (enable smart formulas)

Then click Setup Holidays and then Add each holiday to the default calendar

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/jgcT0Zk7dv/add%20holiday.png (add holiday)

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/zro1FGCMN5/calendar%20setup.png (calendar setup)

Once complete click Back to Settings to exit

Default Calendar

As standard, a single Default Calendar is available and automatically assigned to all employees.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/11KhSBg2hM/default%20calendar.png (default calendar)

Using Additional Calendars

If you require more calendars, simply click Add Calendar, give it a name and click Save. You can then add holidays in the same way as usual.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/ah4ZgsVVVe/add%20calendar.png (add calendar)

NOTE: You must add all required dates for each calendar.

When multiple calendars are defined, you can optionally select a calendar for each employee. If some employees do not need a calendar, simply set up a calendar with no holidays and assign it as required.

Question 2

Using Ignore Types with Smart Formulas

Accepted Answer

Ignore Types

A CSV file often has additional rows that you don't want to import and Upload to Xero.

By entering values in the Ignore Types field, these rows will not be imported or validated in UpSheets and will also not be uploaded to Xero.

The list can have multiple values separated by a comma. A wildcard* can also be used to match other patterns.

All values are Case Sensitive

Example

For example, with the below setup:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/5xVV1sivfQ/ignore%20types.png (ignore types)

Any lines with the following types would be excluded:

Break
Public Holiday
Week
Week 1
Weekly

Any types that don't match would be included, such as:

Ordinary Hours
Breaks
Annual Holiday
WEEK 1

Note - In order for this to work, a Type column must be selected when importing the file.

Question 3

Uploading Smart Formulas with other Types (Earnings Rates)

Accepted Answer

Setup

The default behaviour when using smart formulas is to treat all lines as smart formulas. This is shown in the smart formla settings as Allow Types:

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/8jCYN3OMxx/allow%20types.png (allow types)

If you wish to upload nother earnings types along with this, select the option Allow All Types and Save

Types

For this feature to work, you must supply a Type column in your file to match against your Xero pay items. Any Types that match this column (or have been mapped to one) will be treated normally and not processed as a Smart Formula.

https://malcolm-en-gb.s3.eu-west-1.amazonaws.com/instances/j0x2GOjMS3/resources/KoSXHAVvpw/type%20selection.png (type selection)

Hours

You can optionally select an Hours column to use values from another column than the start/end times.

If a value is present in the hours column, this will be used for the non smart formula types. If no value is present, the start/end times will be used to calulate it. If they are missing an error will be given.