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.

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) See Accumulator Variables
C1 to C7	Time data entry rate of earning code for hourly, daily, weekly, monthly, yearly, pay period, and units respectively. See Pay Rate Variables
F1 to F9	Formula calculation results See Formula Variables
M1 to M9 M30 to M35	Deduction figures (user-enabled in payroll parameters for use on Employee Profile): See Deduction Figure Variables
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
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)
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 See Province and State Variables
V31	Employee province of residence See Province and State Variables
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 Parameters from the Accumulators tab. 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 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.

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