User Guide‎ > ‎

User Defined Formula

You can define User Defined Formula for 
e.g. 
  • [BasicSalary]  /  [DaysInMonth]  /  8
  • [BasicSalary] * 13 / [DaysInYear] / 9 * 1.5
  • [12MthsAverageDailyRate] / 8 / 60 * 2

1. Examples for Rate Factor in OT Type
  • 150
  • [CurrentMonthHourlyRate]*1.5
  • [BasicSalary] / [DaysInMonth] / 8 * 1.5
  • [BasicSalary] * 13 / [DaysInYear] / 9 * 1.5
  • IIF([CurrentMonthHourlyRate]*1.5>80,[CurrentMonthHourlyRate]*1.5,80)

2. Examples for Rate Factor in Late Deduction Type
  • 50
  • [BasicSalary] / 30 / 9 / 60 * 10

3. Examples for Final Payment for Annual Leave Balance based on
  • [12MthsAverageDailyRate]                                  (Statutory)
  • [BasicSalary]/[DaysInMonth]
  • [BasicSalary]/[WorkdaysInMonth]
  • [BasicSalary]/30

4. Examples for Auto Bonus 
  • [BonusAverageRegardedWages] 
  • [BonusLastBasicWages] 
  • [BonusAverageBasicWages]
  • ([BasicSalary]+[Allowance]) * 2
  • [BonusLastBasicWages] * [BonusEmploymentDays] / [BonusElapseDays]

5. Examples for Regular Payments in Employee Master or Employee Class
  • Working years Subsidy: IIF([YearsOfService]>=4,400,IIF([YearsOfService]>=3,300,IIF([YearsOfService]>=2,200,IIF([YearsOfService]>=1,100,0))))
  • Mid-Month Prepaid Allowance: IIF([IsMidMonthPayroll], 2000, 0)
  • Service-End Gratuities: IIF([IsFinalPayment], 20000,0)
  • Probation-End Bonus: IIF([IsProbationEndPayment], 500,0)
  • Travel Allowance (prorata on Join and Termination period): 1000 * [ProratedFactor]
  • Bonus after Probation: IIF([IsProbationPayment], 0,1000)
  • Attendance Bonus: IIF([IsFullAttendance],800,0)
  • Contract End Bonus: IIF([IsContractEndPayment],[BasicSalary],0)

The following characters/variables are acceptable in User Defined Formula

Internal 
0-9   numbers
+ plus
- minus
*     multiply
/ divide
() brackets
IIF     Conditional Statement, Syntax IIF(boolean_expression, true_value, false_value) 
> < =  for IIF boolean_expression
AND OR NOT for IIF boolean_expression

Numeric Variables
[BasicSalary] Basic Salary as at the first day of current payroll period1
[BasicSalaryPreviousMonth] Basic Salary as at the first day of previous payroll period1
[Allowance] Allowance 11
[Allowance2] Allowance 21
[CurrentMonthDailyRate] Current Month Daily Rate2 6
[CurrentMonthHourlyRate] Current Month Hourly Rate3 6 
[CurrentMonthNoPayRate] Current Month No Pay Rate3 6
[12MthsAverageDailyRate] 12 Months Average Daily Rate
[12MthsAverageMonthlyRate] 12 Months Average Monthly Wage5
[PreviousMonthDailyRate]     Previous Month Daily Rate2
[PreviousMonthHourlyRate] Previous Month Hourly Rate3
[PreviousMonthNoPayRate]     Previous Month No Pay Rate3
[12MthsAverageDailyRateForPreviousMonth] 12 Months Average Daily Rate counted from Previous Month
[HourlyRate1] Hourly Rate 1 for Salary by Hourly Employee in Appointment History
[HourlyRate2] Hourly Rate 2 for Salary by Hourly Employee in Appointment History
[HourlyRate3] Hourly Rate 3 for Salary by Hourly Employee in Appointment History
[HourlyRate4] Hourly Rate 4 for Salary by Hourly Employee in Appointment History
[HourlyRate5] Hourly Rate 5 for Salary by Hourly Employee in Appointment History
[HourlyRate6] Hourly Rate 6 for Salary by Hourly Employee in Appointment History
[DailyRate1] Daily Rate 1 for Salary by Daily Employee in Appointment History
[DailyRate2] Daily Rate 2 for Salary by Daily Employee in Appointment History
[DailyRate3] Daily Rate 3 for Salary by Daily Employee in Appointment History
[DailyRate4] Daily Rate 4 for Salary by Daily Employee in Appointment History
[DailyRate5] Daily Rate 5 for Salary by Daily Employee in Appointment History
[DailyRate6] Daily Rate 6 for Salary by Daily Employee in Appointment History
[HourlyRate1ForPreviousMonth] Hourly Rate 1 for Salary by Hourly Employee in Appointment History counted from Previous Month
[HourlyRate2ForPreviousMonth] Hourly Rate 2 for Salary by Hourly Employee in Appointment History counted from Previous Month
[HourlyRate3ForPreviousMonth] Hourly Rate 3 for Salary by Hourly Employee in Appointment History counted from Previous Month
[HourlyRate4ForPreviousMonth] Hourly Rate 4 for Salary by Hourly Employee in Appointment History counted from Previous Month
[HourlyRate5ForPreviousMonth] Hourly Rate 5 for Salary by Hourly Employee in Appointment History counted from Previous Month
[HourlyRate6ForPreviousMonth] Hourly Rate 6 for Salary by Hourly Employee in Appointment History counted from Previous Month
[DailyRate1ForPreviousMonth] Daily Rate 1 for Salary by Daily Employee in Appointment History counted from Previous Month
[DailyRate2ForPreviousMonth] Daily Rate 2 for Salary by Daily Employee in Appointment History counted from Previous Month
[DailyRate3ForPreviousMonth] Daily Rate 3 for Salary by Daily Employee in Appointment History counted from Previous Month
[DailyRate4ForPreviousMonth] Daily Rate 4 for Salary by Daily Employee in Appointment History counted from Previous Month
[DailyRate5ForPreviousMonth] Daily Rate 5 for Salary by Daily Employee in Appointment History counted from Previous Month
[DailyRate6ForPreviousMonth] Daily Rate 6 for Salary by Daily Employee in Appointment History counted from Previous Month
[PAY_TYPE1_AMT] to [PAY_TYPE15_AMT] Pay Type 1 to 15 Amount in Employee Master
[User06] User Defined Field 6 in Employee Master
[User07] User Defined Field 7 in Employee Master
[User08] User Defined Field 8 in Employee Master
[User09] User Defined Field 9 in Employee Master
[User10] User Defined Field 10 in Employee Master
[DaysInMonth] Number of Days of Current Month    
[DaysInPreviousMonth] Number of Days of Previous Month    
[DaysInYear] Number of Days of Current Year (consider leap year)    
[WorkdaysInMonth] Number of Working Days of Current Month 4
[WorkdaysInPreviousMonth] Number of Working Days of Previous Month 4
[WorkdaysInLeavePeriod] Number of Working Days in the Current Leave Period4
[WorkdaysInAttendancePeriod] Number of Working Days in the Current Attendance Period4
[WeekdaysInMonth] Number of Paid Weekdays of Current Month 
[WeekdaysInPreviousMonth] Number of Paid Weekdays of Previous Month
[DaysInLeavePeriod] Number of Days of Current Leave Period
[DaysInAttendancePeriod] Number of Days of Current Attendance Period
[BonusAverageRegardedWages] Using the statutory Average Monthly Wage5 to calculate the bonus. The Regarded Month is based on  Auto Bonus Cover Period.
[BonusLastBasicWages] Read the Basic Salary in Salary Info or Appointment History1. If it is considered Appointment History, it read the Last Salary before the End of Bonus Cover Period.
[BonusAverageBasicWages] Read All Basic Salary figures in Appointment History tab within the Auto Bonus Cover Period, average and return the result.
[BonusAverageBasicWagesMonth] (All Basic Salary figures in Appointment History tab within the Auto Bonus Cover Period / employment days in the Auto Bonus Cover Period * 365 / 12) / 365 * employment days in the Auto Bonus Cover Period
[BonusEmploymentDays] Number of Employment Days within the Cover Period
[BonusElapseDays] Number of Calendar Days within the Cover Period (usually it is 365 except when the Bonus Cover Period covered Feb 29 of leap year, then it is 366)
[SickLeaveDaysInLeavePeriod] Total Number of sick leave days with nature Sick Leave (Statutory) within the Current Leave Period
[InjuryLeaveDaysInLeavePeriod] Total Number of sick leave days with nature Injury Leave (Statutory) within the Current Leave Period
[YearsOfService] Completed Years of Service starting from Join Date (Group Join Date) to the Start Date of the Payroll Period. Return Integer
[MonthsOfService] Completed Months of Service starting from Join Date (Group Join Date) to the Start Date of the Payroll Period. Return Integer
[MPFMaximum] Return Maximum Level of Income (Monthly) of current month for MPF (e.g. 30000)
[MPFMinimum] Return Minimum Level of Income (Monthly) of current for MPF (e.g. 7100)
[ProratedFactor] Return Total Paid Days in Work Period (considered Join Date/Termination Date) / Total Paid Days in Month
[USDExchangeRate] USD Exchange Rate of the Month 7

Boolean Variables (for boolean_expression in IIF)
[IsMidMonthPayroll] Whether it is calling from Mid-Month Payroll Batch. Return Boolean. Default False
[IsFirstPayment] Whether the join date of the employee is within the payroll period. Return Boolean. Default False
[IsFinalPayment] Whether the termination date of the employee is within the payroll period. Return Boolean. Default False
[IsProbationPayment] Whether the payroll period is within Join Date Period and Probation End date period. Return Boolean. Default False
[IsProbationEndPayment] Whether the Probation End date of the employee is within the payroll period. Return Boolean. Default False
[IsContractEndPayment]Whether the contract end date of the employee is within the payroll period. Return Boolean. Default False
[IsFullAttendance]Return True if
  • No Sick Leave/Injury Leave/No Pay Leave taken within Leave Period
  • No Absent in Attendance Record within Attendance Period
  • No Late In in Attendance Record within Attendance Period
  • No Early Out in Attendance Record within Attendance Period
Default False
[IsUDF16] to [IsUDF20] Whether the User-defined Fields 16 to 20 in Employee Master is checked or not. Return Boolean. Default False
[IsEEUSDSalaryPackage]Whether the employee is on USD Salary Package 7

Depending on the settings Payroll Considers Appointment History
Depending on the settings Daily Salary based on