...Menu...

Formula Documentation

Formulas are used throughout the Payroll and Human Resources system. The list below indicates where formulas are presently being used:

1) In the EBD Maintenance and Employee Fixed Maintenance functions,   formulas are used to allow you to create user-defined earnings,  benefits and/or deductions for a particular EBD in either of the  functions listed above. This formula would then be used to calculate  the value of a fixed EBD if a fixed EBD is created for an employee using  this EBD. If there is an overriding formula on the fixed EBD record for a particular employee, then this formula is used instead.

2) In Benefit Code Maintenance in the Human Resources system, formulas are  used to calculate the value of the employer's and employee's portions of a benefit. The formulas entered for the employer's and employee's portions of a benefit are then used to create fixed EBDs in the Payroll system when the benefit is applied.

3) The Benefit Code Maintenance function also uses formulas to determine if an employee is eligible for a particular benefit if you enter a  formula in the "Other Criteria" field.

4) The Payroll Function Keyword Maintenance function allows you to enter formulas which can then be used in other formulas in the Payroll and  Human Resources systems when the payroll function keyword is specified in a formula. Use payroll function keywords simplify formula calculations, and reduce the work necessary when a particular calculation has to be changed.

Formulas can be up to 240 characters in length (i.e., 4 lines of 60 characters each), and can be made up of PROGRESS functions, EBD numbers, EBD types, year-to-date values, payroll keywords, payroll function keywords and constants. Constants refer to any literal numeric, alphanumeric (i.e., character), date, or logical value entered in a formula.

You can use operators such as +, -, *, and / for addition, subtraction, multiplication and division respectively in a formula. Parentheses (i.e., "(" and ")") can also be used to group parts of a formula together.

To use the value of an EBD in a formula, enclose the EBD between two vertical bars (e.g., |101|). The two vertical bars are also referred toas EBD brackets.

If you wish to calculate the total of a specific type of EBD, enclose the type of EBD between EBD brackets (e.g. |E| to calculate the value of all earnings EBD's). The following EBD types can be used in a formula:

     E               Earnings
     NE            Non-taxable earnings
     TB             Federal taxable benefits
     TP             Provincial taxable benefits
     NB            Non-taxable benefits
     ES             Employer's share
     D               Deductions

If you wish to use the hours associated with an EBD or type of EBD on an employee's paycheck in a formula, enter an "H:" before the EBD (e.g., |H:120| or |H:TB|). To use the amount in a formula, enter an "A:" before the EBD (e.g., |A:120| or |A:E|). If neither prefix is entered, the system assumes that you want the amount associated with the EBD or type of EBD (e.g., |E| and |A:E| will come up with the same value.).

If you wish to use year-to-date hours associated with either an EBD or type of EBD in a formula, enter a "YH:" before the EBD (e.g., |YH:101| or |YH:E|). To use the year-to-date amount in a formula, enter a "YA:" before the EBD (e.g., |YA:120| or |YA:D|).

To use a payroll keyword in a formula, enter the payroll keyword between EBD brackets (e.g., |SALHOUR|). The payroll keyword must have been created using the Payroll Keyword Maintenance function prior to its use in a formula. Please refer to the documentation for the Payroll Keyword Maintenance function for more information on how to set up payroll keywords.

If you use a payroll keyword in a formula that refers to a field that has multiple extents (i.e., is an array), you must specify which array element to use. To do this, enter the array element to use between left ([) and right (]) square brackets (e.g., |LANGCODE|[2]). To use a payroll function keyword in a formula, enter the payroll function keyword between EBD brackets (e.g., |ANNSAL|). The payroll function keyword must have been created using the Payroll Function Keyword Maintenance
prior to its use in a formula.  Please refer to the documentation for the Payroll Function Keyword Maintenance function for more information on how to set up payroll function keywords.

Please note that if you have created a payroll keyword and a payroll function keyword with the same name, the system will interpret the keyword between EBD brackets as a payroll function keyword. Also note that EBD types have precedence over both payroll keywords and payroll function keywords.

You can also use PROGRESS functions such as IF THEN ELSE, MAXIMUM, MINIMUM, TRUNCATE, and ROUND in your formulas.

When using the IF THEN ELSE function in your formulas, the following operators can be used to evaluate expressions:

Note: FREQ and SALHOUR are payroll keywords which have been defined as  follows:

      Keyword          File Name     Field Name
     
      FREQ               employee       freq
      SALHOUR      employee      salary-hour

Please also note that you must use the ELSE statement when the IF THEN ELSE function is used. For example, the following formula is invalid:

     IF |FREQ| = 26 THEN |E| * .02

The formula should be entered as follows:

     IF |FREQ| = 26 THEN |E| * .02 ELSE 0

In this case, if the employee's frequency is not biweekly, then the employee's deduction will equal zero.

The formula examples section below will briefly show you how to use PROGRESS functions in formulas. However, for more details, please consult the PROGRESS Language Reference, and PROGRESS Programming Handbook for more information.

Formula Examples


The following are examples of formulas which can be entered:

-To deduct a fixed amount of $10 from an employee's paycheck, the formula would be:

   10

This amount could have also be entered in the "EBD amount" in the  Employee Fixed Maintenance function.  However, if it is done that way,  the amount is deducted once, regardless of the number of paychecks  generated for the employee during the pay period.  Placing the deduction in a formula ensures that the deduction is taken on every paycheck generated for the employee during the pay period.

-If a deduction for a company pension is 5 percent of an employee's earnings, the formula would be:

   |E| * .05

-To give an employee an earning incentive of 25 cents for each hour  worked using EBD 103 (triple time), the formula would be:

   |H:103| * .25

-If an employee is enrolled in a "college fund" savings plan which deducts $1.25 for each child that the employee has, the formula would be:

   1.25 * |CHILDREN|

  where CHILDREN is a payroll keyword set up as follows:

   Keyword          File Name     Field Name
  
   CHILDREN    employee     children

-If you have an employer's share which matches an employee's deduction up to 3 percent of an employee's earnings, the formula would be:

   MINIMUM(|EBD|,.03 * |E|)

where |EBD| is the employee deduction that you are matching.

-To give employees a bonus of 3 percent of the employee's earnings when their commissions exceed $1,000, the formula would be:

       IF |A:122| > 1000 THEN |E| * .03 ELSE 0

  EBD 122, in this case, would be used to track the commissions.

-Health insurance plans are usually deducted after an employee has worked a minimum amount of time. To deduct 2 percent of earnings and taxable benefits for health insurance purposes only if the employee has worked a minimum of 30 days, the formula would be:

     IF |WEEKDATE| - |STARTDTE| > 30 THEN (|A:E| + |A:TB|) * .02 ELSE 0

  where the following keywords have been defined using the Payroll Keyword Maintenance function:

       Keyword           File Name    Field Name
     
       WEEKDATE    week          wk-dte
       STARTDTE      employee    start-dte

-To give an additional $3 bonus to employees whose home shift equals NITE, and who have worked more than 40 hours in a week, and who have worked at least 2 hours of overtime, the formula would be:

  IF |HOMESHFT| = "nite" AND |H:E| > 40 AND |H:102| > 2 THEN 3 ELSE 0

where the following keywords have been defined using the Payroll Keyword Maintenance function:

       Keyword           File Name    Field Name
      
       HOMESHFT    employee      home-shift

-A common situation occurs where an employee is to be deducted a  certain amount of his/her paycheck up to a maximum amount. For example, let's say your company has a dental plan in which the employee contributes 4 percent of his/her earnings up to a fixed amount, let's say $150 per year. After the employee has contributed fully, no further deductions will be made until the end of the year.

  The formula to handle this situation would be:

     IF |YA:599| < 150 THEN MINIMUM(|E| * .04,(150 - |YA:599|)) ELSE 0

  where 599 is the EBD used to track dental plan contributions.

The "minimum" function is used to ensure that the employee does not  exceed $150 if his/her previous deductions plus 4 percent of his/her earnings goes over $150.

-To give a credit of a 5 dollars to employees making less than $10,000 a  year, the following formula would be used:

  IF |ANNSAL| < 10000 THEN 5 ELSE 0

ANNSAL would be a payroll function keyword that would calculate the employee's annual salary.  The formula associated with the ANNSAL payroll function keyword would be as follows:

IF |SALHOUR| THEN |SALARY| * |FREQ| ELSE |RATE| * 40 * 52

where the following keywords have been defined using the Payroll Keyword Maintenance function:

       Keyword           File Name    Field Name
      
       SALHOUR      employee      salary-hour
       SALARY          employee     salary
       FREQ               employee      freq
       RATE               employee      rate

-To give a bonus of 2 percent of earnings if the employee speaks French, the following formula would be used.

  IF |SPKFRENC| THEN .02 * |E| ELSE 0

where SPKFRENC would be a payroll function keyword with the  following formula (n.b., The formula would return a logical
value.)

  (|LANG|[1] = "French" AND |LSPK|[1]) OR
  (|LANG|[2] = "French" AND |LSPK|[2]) OR
  (|LANG|[3] = "French" AND |LSPK|[3]) OR
  (|LANG|[4] = "French" AND |LSPK|[4]) OR
  (|LANG|[5] = "French" AND |LSPK|[5]) OR
  (|LANG|[6] = "French" AND |LSPK|[6])

where the following keywords have been defined using the Payroll Keyword Maintenance function:

      Keyword           File Name    Field Name
      
       LANG             hr-employee   language
       LSPK              hr-employee   lang-spoken

Validation

After you enter a formula, the system removes all unnecessary spaces from the formula, and places spaces around the +, -, *, and / operators.

The system will also check to see if the EBD numbers entered are within EBD brackets and are within the range of allowable EBD's, and will makesure that EBD numbers, EBD types, year-to-date values, payroll keywords and payroll function keywords are properly enclosed within EBD brackets.

It will then check the formula to see if it is "mathematically correct".If there is an error in the formula, you will receive an error message.

After the system has checked that a formula is "mathematically correct",it will check the syntax of the formula by generating a PROGRESS procedure for the formula.   If there is an error in your formula, you will receive an error message.   Please see the error messages below for reasons why your formula may be in error.

Notes


-When entering numbers, do not use any character as a thousands separator (e.g., 1,000,000), and use the period (.) as a decimal point  (e.g., 12.56).

-When entering alphanumeric values, make sure that they are contained within single or double quotes (e.g., "ABC").

-Logical values should be entered as either yes, true, no or false, with no quotes surrounding the value (e.g., yes).

-Since the "/" character is used as a division symbol, you must use the PROGRESS date function to enter a literal date.  For example, the following formula segment checks if the employee's birth date is  greater than September 24, 1964:

       IF |BIRTHDTE| > DATE(9,24,1964) THEN ... ELSE ...

The PROGRESS date function uses the following syntax:

  DATE(MONTH,DAY,YEAR)

The year should be entered as a 4 digit year.

-If your formula includes very large integer constants (i.e., over 1000000000), you should enter them as a decimal (e.g., 9999999999.0).

- You do not have to use an "if" statement if the formula returns a   logical value. For example, the two formulas below are equivalent:

  |ANNSAL| > 30000
and
     IF |ANNSAL| > 30000 THEN YES ELSE NO

The system does not check whether or not the array elements specified are within the limits of the field associated with a payroll keyword when the formula is entered. This is only done when the formula is calculated (i.e., During the Batch Cheque Creation, Manual Cheque, and Apply Benefits functions).

- The system does not check to see if a formula has a circular reference,  (i.e., A payroll function keyword, which by referring to other payroll function keywords in its function formula, eventually refers back to itself) when the formula is entered.   Circular references are only checked when the formula is calculated (i.e., During the Batch Cheque Creation, Manual Cheque, and Apply Benefits functions).

- The PROGRESS procedure used to verify the syntax of the formulas you  enter is stored in the directory from which your ran your   software. In order to determine the current working directory, the  system uses the following programs, depending on the operating system  used to run the payroll and human resources system:

FORMULA ERROR/WARNING MESSAGES


ERROR: Length of expanded formula is greater than <nnn> characters.

Reason: The length of the formula after being fully expanded is more
than <nnn> characters.
Action: Make sure that your formula, after being fully expanded, is
less than <nnn> characters.  Try reducing the complexity of your
formula, if possible.

ERROR: There is no leading opening '(' parenthesis.
---------------------------------------------------
Reason: You have entered a closing parenthesis in your formula, but there
is no opening parenthesis before it.  As a result, the formula
cannot be mathematically correct.
Action: Make sure that there is at least one opening parenthesis before
the first closing parenthesis in your formula.

ERROR: No closing EBD bracket after opening EBD bracket at position
       <position>
-------------------------------------------------------------------
Reason: You are missing a closing EBD bracket.
Action: Make sure that there is a closing EBD bracket for each opening
EBD bracket.

ERROR: You cannot use a function keyword in its own function keyword
       formula.
--------------------------------------------------------------------
Reason: Your function formula contains a reference to the same payroll
function keyword as you are editing.  For example, if you are
editing payroll function keyword ANNSAL, the following function
formula would be invalid: .01 * |ANNSAL|.
Action: Make sure that the function formula does not contain a reference
to the payroll function keyword you are editing.

ERROR: Prefixes cannot be used with user-defined keywords and/or
       functions.
----------------------------------------------------------------
Reason: You have used the "H:", "A:", "YH:" or "YA:" prefix with a
payroll keyword and/or payroll function keyword (e.g.,
|H:FEDALL|).  Since payroll keywords and payroll function keywords
are not based on hours or amounts, there is no use in associating
a prefix with a payroll keyword or payroll function keyword.
Action: Make sure that there are no prefixes before payroll keywords and/or
payroll function keywords in your formulas.

ERROR: No EBD is specified between EBD brackets.
------------------------------------------------
Reason: You have not specified an EBD or type of EBD between EBD brackets.
This may also occur if you have entered a prefix (e.g., "H:"),
but did not specify which EBD or type of EBD to use.
Action: Make sure that an EBD or type of EBD is entered.

ERROR: Invalid character in EBD: <character> at position <position>
-------------------------------------------------------------------
Reason: You have entered a character which is not a number between EBD
brackets. This can happen if you have misspelled a payroll
keyword or payroll function keyword in your formula.
Action: Make sure that the value between EBD brackets is numeric. If
you are trying to use a payroll keyword and/or a payroll function
keyword, make sure that it is spelled correctly.

ERROR: EBD <EBD> is out of range.
---------------------------------
Reason: You are using an EBD which is greater than 9999 in your formula.
Action: Make sure that the EBD's used in formulas are between 100
and 9999.

ERROR: An EBD cannot use its own EBD number in a formula.
---------------------------------------------------------
Reason: Your formula includes a reference to the EBD you are editing.
For example, if you are editing EBD 520, the following formula
would be invalid: (.01 * (|101| + |520|)).
Action: Do not include references to the same EBD which you are editing
in your formulas.

WARNING: EBD <EBD> does not exist in the EBD file.
--------------------------------------------------
Reason: The EBD number you are using does not exist in the EBD file.
Action: Make sure that the EBD is created before attempting to
use it in a formula.

ERROR: There is no expression before the '/' or '*' operator.
-------------------------------------------------------------
Reason: You are attempting to multiply or divide but there is no number
or expression before the multiply or divide sign (e.g. / 6).
Action: Make sure that there is an expression before the multiplication
('*') or division ('/') operators.

ERROR: An operator (+,-,/,*) cannot be the last character in a formula.
-----------------------------------------------------------------------
Reason: You have entered an operator as the last character in a formula.
Since there is nothing after the operator, the formula would be
invalid.
Action: Make sure that an operator is not the last character in your
formula.

ERROR: A decimal point cannot be the last character in a formula.
-----------------------------------------------------------------
Reason: You have entered a decimal point as the last character in a
formula. Since there is nothing after the decimal point, the
formula would be invalid.
Action: Make sure that a decimal point is not the last character in your
formula.

ERROR: Cannot have more than one decimal point in a number.
-----------------------------------------------------------
Reason: You have entered a number which has more than one decimal point
in it (e.g., 1.233.4).  A number entered like that is not
acceptable.
Action: If you use numbers with decimal points in your formula, make sure
that there is only one decimal point in it.

ERROR: No closing array bracket after opening array bracket at
       position <position>
--------------------------------------------------------------
Reason: You are missing a closing array bracket (i.e., "]").
Action: Make sure that there is a closing array bracket for each opening
array bracket.

ERROR: No array element is specified.
-------------------------------------
Reason: You have not specified which array element to use between array
brackets.
Action: Make sure that you specify which array element to use.

ERROR: Invalid character in array element: <character> at position
       <position>
------------------------------------------------------------------
Reason: You have entered a character which is not a number between array
brackets.
Action: Make sure that the value between array brackets is numeric.

ERROR: You cannot use array element 0 in a formula.
---------------------------------------------------
Reason: You have used array element 0 in a formula.  The array element
specified must be greater than or equal to 1.
Action: Make sure that the value entered between array brackets is greater
than or equal to 1.

ERROR: No closing quote after opening quote at position <position>
------------------------------------------------------------------
Reason: You are missing a closing quote.
Action: Make sure that there is a closing EBD bracket for each opening
quote.

ERROR: Missing <number> closing ')' parentheses.
------------------------------------------------
Reason: Your formula has more opening parentheses than closing parentheses.
Action: Make sure that you have same number of opening and closing
parentheses.

ERROR: Missing <number> opening '(' parentheses.
------------------------------------------------
Reason: Your formula has more closing parentheses than opening parentheses.
Action: Make sure that you have the same number of opening and closing
parentheses.

ERROR: There is an error in your formula.
-----------------------------------------
Reason: Your formula has an error in it due to one or more, but not
limited to, the following reasons:

- You have used PROGRESS functions in your formula, and have not
  spelled the function names correctly.

- You have given a PROGRESS function the incorrect number
  of parameters in your formula.  For example, the following
  formula is invalid because the MAXIMUM function requires
  two parameters:

     maximum(|ANNSAL|)

- You have not used an ELSE statement when using an IF statement.
  For example, the following formula is invalid.

  IF |ANNSAL| > 30000 THEN |E| * .01

- You have not left spaces between PROGRESS functions and/or
  payroll keywords and/or payroll function keywords. For example,
  the following formula is invalid:

  if |ANNSAL| > 30000AND|SALHOUR|

  The system cannot calculate your formula in this case.

- You have referred to a field that has multiple extents in it
  (i.e., is an array), and you have not specified which array
  element to use.  For example, the following formula segment
  is invalid.

IF |LANGCODE| = "French" THEN ... ELSE ...

  where LANGCODE is a payroll keyword that refers to a field
  that is an array.

- The PROGRESS procedure generated has a statement with more
  than 4,096 characters in it.  This may occur if you are
  using many payroll function keywords in your formula.

- You do not have the proper read/write permission to create
  the PROGRESS procedure, and/or the compiled program in the
  current working directory.

- You do not have enough disk space to generate the PROGRESS
  procedure required to calculate your formula, and/or the
  compiled program.

Action: Make sure that you have entered your formula correctly, that
PROGRESS functions are spelled and used correctly, and you have
left spaces before and after PROGRESS functions and/or payroll
keywords and/or payroll function keywords.  If you still cannot
determine what the problem is with your formula, verify the
the PROGRESS procedure, in your current directory which will
be called:

   prg<terminal-no>

   (e.g., prgttyh7)

where <terminal-no> is the terminal which you are using. This is
the procedure used to calculate the value of your formula. You
can verify this procedure by using the PROGRESS editor, or the
text editor of your choice. If you are still unable to determine
what the problem is, please contact your VAR and/or VARNET
technical support.

If there is not enough disk space to generate the PROGRESS
procedure necessary to calculate the value of your formula,
or you do not have read/write permission in your current
working directory, then please see your System Administrator.

ERROR: Formula results in a division by zero or is undefined.
-------------------------------------------------------------
Reason: Your formula is valid, but is returning an undefined (i.e., ?)
value.
Action: Make sure that you are not dividing by zero in your formula,
and that payroll keywords and/or payroll function keywords,
do not result in an undefined value.