Open navigation

Formula Variables

Formula Variables allow you to include data stored in Avanti in your formulas. These variables will pull values from the Employee Profile, current payroll, year-to-date and life-to-date totals, and other earning or deduction/benefit codes.

A brief summary of the variables is included below.

  
VariableDescription

A1 to A30

Current Period Accumulator amounts 1-30. Types: Amount, Units, Amt (Week 1), Amt (Week 2), Units (Week 1), Units (Week 2)

C1 to C7

Time data entry rate of earning code for hourly, daily, weekly, monthly, yearly, pay period, and units respectively.

F1 to F9

Formula calculation results

M1 to M9 M30 to M35

Deduction figures (user-enabled in payroll parameters for use on Employee Profile):

M10

Pay Period ending date as number of days since Jan 1, 1900

M11 to M22

Other dates as number of days since Jan 1, 1900 (user-enabled in payroll parameters for use on Employee Profile)

see Date Variables

M36

Payment Date (YYYYMMDD)

M37

Pay Period Start Date as number of days since Jan 1, 1900

P1 to P30

Previous period accumulator amounts 1-30. See A1-A30

R1 to R6

Employee Profile rate of pay for hourly, daily, weekly, monthly, yearly, and pay period

T1 to T20

Text fields (user-enabled in payroll parameters for use on Employee Profile)

see Text Field Variables

W1 to W7

Time data entered units of work for hours, daily, weekly, monthly, yearly, pay period, and units

W8

Numeric value of the Time Data Entry UOM: 1-Hours, 2-Days, 3-Weeks, 4-Months, 5-Years, 6-PayPeriods, 7-Units

B0

Benefit amount result. B0 can be referenced in a different code by indexing (B0#xxx), although not in the same code as it creates a circular reference.

B1

YTD Benefit amount

B2

LTD Benefit amount

B4

MTD Benefit using the month and year from the pay ending date

B5

MTD total Benefits using the month and year from the payment date

D0

Deduction amount result. D0 can be referenced in a different code by indexing (D0#xxx) although not in the same code as it creates a circular reference.

D1

YTD Deduction amount

D2

LTD Deduction amount

D3

Total current Deductions

D4

MTD Deduction using the month and year from the pay ending date

D5

MTD total Deductions using the month and year from the payment date

E0

Eligible earning result.

E1

YTD Eligible Earnings

E2

LTD Eligible Earnings

E4

MTD Eligible Earnings using the month and year from the pay ending date

E5

MTD total Eligible Earning base using the month and year from the payment date

G0

Gross Earnings

G1

YTD Earnings per selected code

G2

LTD Earnings per selected code

G4

MTD Earnings using the month and year from the pay ending date

G5

MTD Earnings using the month and year from the payment date

H1

YTD Units

H2

LTD Units

H4

MTD Units using the month and year from the pay ending date

H5

MTD Units using the month and year from the payment date

J1

Spouse/Common-law Spouse Age in years from Dependents function

J2

Spouse/Common-law Spouse Gender (0=male, 1=female, 2=Unspecified) from Dependents function

J3

Spouse/Common-law Spouse Birthdates from Dependents function

J4

Spouse/Common-law Spouse Smoker (0=non, 1=smoker) from Dependents function

J5

Total Dependents from Dependents function

K1

Benefit Rate from Benefits Administration module

K2

Benefit Coverage amount from Benefits Administration module

L0

Current period Leave Units

L1

Leave carried forward Dollars

L2

Leave accrued Dollars

L3

Leave taken Dollars

L4

Leave remaining Dollars (L1 + L2 - L3)

L5

Leave carried forward Units

L6

Leave accrued Units

L7

Leave taken Units

L8

Leave remaining Units (L5 + L6 - L7)

U0

Eligible Unit result

U1

YTD Eligible Units

U2

LTD Eligible Units

U4

MTD Unit Base using the month and year from the pay ending date

U5

MTD Unit Base using the month and year from the payment date

V1

Age in years

V2

FTE (Full Time Equivalent) to 4 decimal places

V3

Life to date service hours

V4

Marital Status (0=other, 1=single, 2=married, 3=common law)

V5

Service months since seniority date

V6

CPP deduction amount

V7

CPP benefit amount

V8

Gender (0=male, 1=female,2=unspecified)

V9

Hours per day from pay group

V10

Days per week from pay group

V11

Weeks per year from pay group

V12

Months per year from pay group

V13

Pay periods per year from pay group

V14

Initial hire date as number of days since Jan 1, 1900

V15

Last hire date as number of days since Jan 1, 1900

V16

Birth date as number of days since Jan 1, 1900

V17

Seniority date as number of days since Jan 1, 1900

V18

Vacation date as number of days since Jan 1, 1900

V19

CPP YMPE (year’s maximum pensionable earnings) before rounding

V20

Smoker (0=non, 1=smoker)

V21

Vacation months since vacation date

V22

CPP YMPE (year’s maximum pensionable earnings) after rounding

V23

GST rate 0.05

V24

HST rate is zero except for the following provinces:

British Columbia0.12
New Brunswick0.13
Newfoundland0.15
Ontario0.13
Nova Scotia0.15
Prince Edward Island0.15

V25

EHT rate for Ontario is 0.0195

V26

PST rate as indicated on Provincial Tax table for employee province of employment

V27

Quebec Health Services Fund rate from Pay Group as a decimal factor

V28

Current pay period number

V29

Days since last hire divided by days in pay period to maximum 100%

V30

Employee province of employment (See following table for values)

V31

Employee province of residence (See following table for values)

V32

Employee Pay Group

V33

YTD service hours

V34

Deduction/benefit user defined Variable 1

V35

Deduction/benefit user defined Variable 2

V36

CPP/QPP Earnings total. This variable can be used to calculate Quebec Health Tax. When using this variable in an earning code ensure all earnings are included, by using the highest earning code 499 or setting the Calc. Step to 9.

V37

Employee Vacation Group

V38

Pay period days in current pay period

V39

Pay period days employed in current pay period

V40

Provincial Sales Tax Rate from the Provincial Tax Table based on the employee’s province of residence found on the Personal Tab of the Employee Profile.

V41

Vacation Percentage based on the employee’s vacation group. Vacation Percentage is found on the Employment Data Tab of the Employee Profile.

Accumulator Variables

Accumulator variables allow you to use the current value of a payroll accumulator in a formula.

  • The accumulators can be viewed and setup from Canadian Payroll > Installation and Maintenance > Payroll Parameters from the Accumulators tab. The variable name will appear to the left of the accumulator it represents.

    1 7 2019 10 23 35 AM
  • Accumulators are run per-employee, so each employee will have their own values for the accumulators.

Pay Rate Variables

Pay Rate variables report the amount of pay per measure (days, hours, weeks, etc.) for the current earning/deduction/benefit code. The current code is the code containing the formula.

The rate is converted using the rates from the Pay Group calendar and the current pay rate entered for the code.

1 7 2019 11 02 51 AM
Figure 1. Pay Group calendar rates found at: Canadian Payroll > Employer Payroll Tables > Pay Groups.

For example, an employee is paid 35 hours of regular earnings during a pay-period at $25 per hour. The rate of $25 per hour will be converted to each of the other calendar rates using the rates from the Pay Group Calendar as follows:

C1 (hourly rate)= $25.00 per hour
C2 (daily rate)= $200.00 per day
C3 (weekly rate)= $1,000.00 per week
C4 (monthly rate)= $4,333.33 per month
C5 (yearly rate)= $52,000.00 per year
C6 (pay period rate)= $2,000.00 per pay period
Notes:
  • The weekly, monthly and pay period rates are calculated by calculating the yearly rate first (from the hourly, weekly, and weeks per year rates), then dividing by the corresponding rate.

    $25 * 8 * 5 * 52 / 12 = $4,333.33 per month
  • The Units rate cannot be converted to calendar rates. That means that an hourly rate will not be converted to a Unit rate. To use a per Unit rate, the Unit rate must be explicitly entered in time/payroll data entry.

Formula Variables

Formula Variables are used within a formula to separate long calculations into different parts. They are mostly used to make formulas easier to read and understand.

These variables will be assigned the value of the expression entered on the corresponding formula line. For example, in the following figure:

  • the F1 formula variable will hold the value 6.00.

  • the F2 formula variable will hold the evaluated value of 2.4.

1 7 2019 11 24 57 AM

Formula variables are calculated in ascending order from F1 to F9 from the corresponding formula lines. This means that only formula variables above the current line should be referenced from that expression.

Deduction Figure Variables

Deduction Figure variables let you use the values of custom fields on the Employee Profile in formulas.

Deduction figures can be viewed and setup from Canadian Payroll > Installation and Maintenance > Payroll Parameters from the Profile Field Names tab.

1 7 2019 11 31 10 AM

The Deduction figures can be entered on the Employee Profile from the Values tab:

1 7 2019 11 33 59 AM

Date Variables

Date variables let you use custom dates from the Employee Profile in formulas.

Dates can be viewed and setup from Canadian Payroll > Installation and Maintenance > Payroll Parameters from the Profile Field Names tab.

1 7 2019 11 38 18 AM

The dates can be entered on the Employee Profile from the Dates tab:

1 7 2019 11 39 01 AM

Text Field Variables

Text fields let you use custom fields from the Employee Profile in formulas.

Text fields can be viewed and setup from Canadian Payroll > Installation and Maintenance > Payroll Parameters from the Profile Field Names tab.

1 7 2019 11 41 01 AM

The text fields can be entered on the Employee Profile from the Values tab:

1 7 2019 11 42 00 AM

J
Jack is the author of this solution article.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.