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.
Variable | Description |
|---|---|
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 | 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) |
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, 3=non-binary) 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 |
K2 | Benefit Coverage amount from Benefits Administration |
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, eCPP2, QPP, and eQPP2 deduction amount |
V7 | CPP, eCPP2, QPP, and eQPP2 benefit amount |
V8 | Gender (0=male, 1=female, 2=unspecified, 3=non-binary) |
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.12New Brunswick0.13Newfoundland0.15Ontario0.13Nova Scotia0.15Prince 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 |
V31 | Employee province of residence |
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. |
V42 | CPP/QPP YAMPE (including eCPP2 & eQPP2, the year's additional maximum pensionable earnings) before rounding |
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 Parametersfrom theAccumulatorstab. The variable name will appear to the left of the accumulator it represents.
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 working times from the Pay Group and the current pay rate entered for the code.
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
Additional Details
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.
To get the monthly calculation: $25 * 8 * 5 * 52 / 12 = $4,333.33 per monthThe 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.
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.
The Deduction figures can be entered on the Employee Profile from the Values tab:
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.
The dates can be entered on the Employee Profile from the Dates tab:
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.
The text fields can be entered on the Employee Profile from the Values tab:
Province and State Variables
These values can be used in V30 and V31.
Value | Abbreviation | Province/State | Value | Abbreviation | Province/State | |
1 | NL | Newfoundland & Labrador | 34 | MD | Maryland | |
2 | NS | Nova Scotia | 35 | ME | Maine | |
3 | PE | Prince Edward Island | 36 | MI | Michigan | |
4 | NB | New Brunswick | 37 | MN | Minnesota | |
5 | QC | Quebec | 38 | MO | Missouri | |
6 | ON | Ontario | 39 | MS | Mississippi | |
7 | MB | Manitoba | 40 | MT | Montana | |
8 | SK | Saskatchewan | 41 | NC | North Carolina | |
9 | AB | Alberta | 42 | ND | North Dakota | |
10 | BC | British Columbia | 43 | NE | Nebraska | |
11 | NT | Northwest Territories | 44 | NH | New Hampshire | |
12 | YT | Yukon Territory | 45 | NJ | New Jersey | |
13 | NU | Nunavut | 46 | NM | New Mexico | |
14 | AL | Alabama | 47 | NV | Nevada | |
15 | AK | Alaska | 48 | NY | New York | |
16 | AR | Arkansas | 49 | OH | Ohio | |
17 | AZ | Arizona | 50 | OK | Oklahoma | |
18 | CA | California | 51 | OR | Oregon | |
19 | CO | Colorado | 52 | PA | Pennsylvania | |
20 | CT | Connecticut | 53 | RI | Rhode Island | |
21 | DC | District of Columbia | 54 | SC | South Carolina | |
22 | DE | Delaware | 55 | SD | South Dakota | |
23 | FL | Florida | 56 | TN | Tennessee | |
24 | GA | Georgia | 57 | TX | Texas | |
25 | HI | Hawaii | 58 | UT | Utah | |
26 | IA | Iowa | 59 | VA | Virginia | |
27 | ID | Idaho | 60 | VT | Vermont | |
28 | IL | Illinois | 61 | WA | Washington | |
29 | IN | Indiana | 62 | WI | Wisconsin | |
30 | KS | Kansas | 63 | WV | West Virginia | |
31 | KY | Kentucky | 64 | WY | Wyoming | |
32 | LA | Louisiana | 65 | ZZ | Outside North America | |
33 | MA | Massachusetts |