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 OT Type
  • 150
  • [CurrentMonthHourlyRate]*1.5
  • [BasicSalary] / [DaysInMonth] / 8 * 1.5
  • [BasicSalary] * 13 / [DaysInYear] / 9 * 1.5

2. Examples for 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
  • 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)

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
[CurrentMonthHourlyRate] Current Month Hourly Rate3
[CurrentMonthNoPayRate] Current Month No Pay Rate3
[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
[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 Position History tab within the Auto Bonus Cover Period, average and return the result.
[BonusAverageBasicWagesMonth] (All Basic Salary figures in Position 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
[SickLeave2DaysInLeavePeriod] Total Number of sick leave days with nature Sick Leave 2 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)

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
[IsProbationEndPayment] Whether the termination date of the employee is within the payroll period. Return Boolean. Default False
[IsUDF16] to [IsUDF20] Whether the User-defined Fields 16 to 20 in Employee Master is checked or not. Return Boolean. Default False

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