1. Take the absolute value
Name：absolute value
Function Name：abs(double value)
Parameter List：value number type
Eg：abs(-1.25) result is 1.25
Special note: none
2. Round off
Name: round off
Function Name：round(double value, int digit)
Parameter List：value number type，digit integer (not required)
Eg：round(2.5645, 2) result is 2.56，round(2.5645) result is 3
Special note: If digit is not filled and only one parameter is filled in, it will return a rounded integer
3. Round up
Name: round up
Function Name：ceil(double value)
Parameter List：value number type
Eg：ceil(2.45) result is 3.0
Special note: none
4. Round down
Name: round down
Function Name：floor(double value)
Parameter List：value number type
Eg：floor(2.45) result is 2.0
Special note: none
5. Take the day
Name：day value
Function Name：day (date value)
Parameter List：value date type
Eg：day(‘2019-12-20’) result is 20
Special note: none
6. Take the month
Name：month value
Function Name：month (date value)
Parameter List：value date type
Eg：month(‘2019-12-20’) result is 12
Special note: none
7. Take the year
Name：month value
Function Name：year(date value)
Parameter List：value date type
Eg：year(‘2019-12-20’) result is 2019
Special note: none
8. Date difference
Name：Calculate the time difference between two specified dates
Function Name：DateDiff (Date Part, StartDate, EndDate)
Parameter List：
Date Part Optional Range DAY / MONTH / YEAR
StartDate/EndDate Format：YYYY-MM-DD
Eg：
DateDiff (DAY, '2019-01-01', '2019-12-31') result is 364
DateDiff (MONTH, '2019-01-01', '2019-12-31') result is11
9. Date Calculation
Name: Calculate a specific date
Function name：DateAdd (Date Part, number, Date)
Parameter List：
Date Part Strings Optional Range DAY/MONTH/YEAR
Date Full Date YYYY-MM-DD
Number Integer, date value calculated forward (positive number) or backward (negative number).
Eg：
DateAdd (DAY, -5, '2019-12-20') result is 2019-12-15
DateAdd (MONTH, 5, '2019-12-20') result is 2020-05-20
10. Pay item monthly average
Name：PayitemMthlyAverage
Function description: Specify a time period for a pay item, and calculate the monthly average salary of this period of time.
Method：PayitemMthlyAverage (Payitem, MonthDiffNumber, 0)
Parameter Description:
Payitem：pay items in the system
MonthDiffNumber：Retrieve the specified number of months in the past from the current month (not including the current month).
Eg：
The current salary calculation cycle is 2019-06-01 to 2019-06-30
The basic salary of an employee from March to May is 10,000, 15,000, and 20,000 respectively.
Calculate the monthly average of the employee’s basic salary for the three months before the current salary cycle: PayitemMthlyAverage (\'BasicSalary\', 3, 0) ，result is 15000 .
11. Monthly salary summary and sum
Name：PayitemMthlyTotal
Function description: Specify a time period for a pay item, and calculate the total salary of this period of time.
Method：PayitemMthlyTotal (Payitem, MonthDiffNumber, 0)
Parameter Description:
Payitem：Take the pay items in the system, you can not input by yourself.
MonthDiffNumber：Retrieve the specified number of months in the past from the current month (not including the current month).
Eg：
The current salary calculation cycle is June.
The basic salary of an employee from March to May (named BasicSalary in the system) is 10000, 15000, 20000, respectively.
Calculate the sum of the employee’s basic salary for the three months before the current salary cycle: PayitemMthlyTotal ('BasicSalary', 3, 0) ，result is 45000.
12. Pay run cycle start date
Name：PayRunCycleStartDate
Function description: back to pay run cycle start date.
Method：PayRunCycleStartDate ()
Parameter description: no parameter
Eg：
suppose the current pay run cycle is 2020-03-01 to 2020-03-31.
PayRunCycleStartDate() result is 2020-03-01
13. Pay run cycle end date
Name：PayRunCycleEndDate
Function description: back to pay run cycle end date.
Method：PayRunCycleEndDate ()
Parameter description: no parameter
Eg：
The current pay run cycle is 2020-03-01 to 2020-03-31.
PayRunCycleEndDate () result is 2020-03-31.
Eg：
According to the function of 12 and 13, calculate the time difference between two dates.
The current pay run cycle is 2020-03-01 to 2020-03-31.
var a = PayRunCycleStartDate ()
var b = PayRunCycleEndDate ()
DateDiff (DAY, ‘a’, ‘b’) result is 30
14. Pay run attendance date
Name：PayRunAttendStartDate
Function description: back to the start date of the attendance period used in the current salary calculation period..
Method：PayRunAttendStartDate ()
Parameter description: no parameter
Eg：
The current pay run cycle of the attendance period is 2020-03-01 to 2020-03-31.
PayRunAttendStartDate () result is 2020-03-01.
Name：PayRunAttendEndDate
Function description: back to the end date of the attendance period used in the current salary calculation period.
Method：PayRunAttendEndDate ()
Parameter description: no parameter
Eg：
The current pay run cycle of the attendance period is 2020-03-01 to 2020-03-31.
PayRunAttendEndDate () result is 2020-03-31
Eg:
According to the function of 14 and 15, calculate the time difference between two dates.The current pay run cycle of the attendance period is 2020-03-01 to 2020-03-31.
var a = PayRunAttendStartDate ()
var b = PayRunAttendEndDate ()
DateDiff (DAY, a, b) result is 30
16. Pay run pay date
Name：PayRunPayDate
Function description: back to the current pay run pay date.
Method：PayRunPayDate ()
Parameter description: no parameter
Eg：
Suppose the current pay run cycle is 2020-03-01 to 2020-03-31，pay run pay date is 2020-04-01.
PayRunPayDate () result is 2020-04-01.
17. Pay run period in service calendar days
Name：PayRunPeriodInSerCalDays
Function description: back to the current pay run period in service calendar days. （including Saturday, Sunday）
Method：PayRunPeriodInSerCalDays ()
Parameter description: no parameter.
Eg:
Suppose the current pay run cycle is 2020-03-01 to 2020-03-31：
If the employee has been employed before 2020-03-01，PayRunPeriodInSerCalDays () result is 31.
If the employee has been employed before 2020-03-16, PayRunPeriodInSerCalDays () result is 16.
18. Pay run period in service work days
Name：PayRunPeriodInSerWorkDays
Function description: back to the current pay run period in service work days. （not including Saturday, Sunday）
Method：PayRunPeriodInSerWorkDays ()
Parameter description: no parameter.
Eg:
Suppose the current pay run cycle is 2020-03-01 to 2020-03-31, the working days stipulated by the company are Monday to Friday:
If the employee has been employed before 2020-03-01，PayRunPeriodInSerWorkDays () result is 23.
If the employee has been employed before 2020-03-16，PayRunPeriodInSerWorkDays () result is 12.
19. Monthly Payroll Base Days
Name：MonthlyPayrollBaseDays
Function description: Under the current payroll policy, calculate the current month's due income proportionally based on how many days are the base number.
Method：MonthlyPayrollBaseDays ()
Parameter description: no parameter.
Eg：
Suppose the current payroll policy is monthly，according to the calendar days cycle.
The current month is January. There are 31 calendar days in total.
MonthlyPayrollBaseDays () result is 31.
20. Total number of statutory holidays
Name：PayRunPeriodSHDays
Function description: The total number of statutory holidays in the current pay run period.
Method：PayRunPeriodSHDays ()
Parameter description: no parameter.
Eg：
Suppose the current pay run cycle is 2020-04-01 to 2020-04-30.
2020-04-04 Ching Ming Festival is a statutory holiday.
PayRunPeriodSHDays () result is 1.
21. Get employee information
Name：GetEmployeeInfo
Function description: take the value corresponding to the employee information (numerical value, date or text, etc.).
Method：GetEmployeeInfo (‘field name’)
Parameter description: Field name should be corresponding to the display name in the system.
Eg：GetEmployeeInfo (\'lastWorkingDate\') result is 2020-02-29.
22. The total number of leave days of the customized leave type.
Name：AttendPeriodLeaveDays
Function description: the total number of leave days of the customized leave type in the current pay run attendance period.
Method：AttendPeriodLeaveDays (‘Leave Type’)，result is days.
Parameter description: Leave type is added manually by users in the system.
Eg：AttendPeriodLeaveDays ('Bereavement Leave')
Eg in detail：
Suppose the current pay run cycle is 2020-03-01 to 2020-03-31，the attendance cycle is consistent.
Suppose the employee takes 3 days of bereavement leave in the current month (bereavement leave is customized).
AttendPeriodLeaveDays ('Bereavement Leave') result is 3.
23. Count attendance unusual status by days
Name：CountAttendStatusByDay
Function description: In the attendance cycle corresponding to the pay run cycle, the attendance status (late, early leave, absent) counts the number of occurrences of the status in units of days (the total number of days with late arrival status within a month).
Method：CountAttendStatusByDay (Status Item, Range in mins)，result is the total number of days.
Parameter description:
Status Item: Late, Early Leave, Absence
Range: Minutes
Eg:
Suppose the current pay run cycle is 2020-03-01 to 2020-03-31，the attendance cycle is consistent. An employee has a record of being late for 5 days in the current month. Two days were within 10 minutes late, and the other three days were 20 minutes：
CountAttendStatusByDay ('Late', 0-10) result is 2
CountAttendStatusByDay ('Late', 10-30) result is 3
CountAttendStatusByDay ('Late', 0-30) result is 5
24. Sum of total minutes of unusual attendance status
Name：AttendStatusSumByMins
Function description: Sum of total minutes of unusual attendance status（late, early leave, absent）
Method：AttendStatusSumByMins (\'Status Item\')，result is total minutes
Parameter description:
Status Item: Late, Early Leave, Absence
Eg:
Suppose the current pay run cycle is 2020-03-01 to 2020-03-31，the attendance cycle is consistent. An employee has a record of being late for 3 days in the current month, including 5 minutes, 8 minutes, 10 minutes. AttendStatusSumByMins ('Late') result is 23 minutes (5+8+10).
25. Get employee’s statutory holidays within the specified time range
Name：GetEmpSHDay
Function description: from the join date to the end of pay run cycle/ terminated date(Whichever is the earliest) , to get employee’s statutory holidays.
Method：GetEmpSHDay (Date Part, Number )
Parameter description:
Date Part：DAY / MONTH / YEAR （select 1 from 3）
Number：Date type ‘s unit of measurement, as an integer
Eg1 :
An employee’s joining date is 2020-01-01，now in service.
The current pay run cycle is 2020-04-01 to 2020-04-30.
If you want to count how many days of statutory holidays during the period from one month after the employee joined to 2020-04-30，then use the formula GetEmpSHDay (MONTH, 1) .
Eg2 :
An employee’s joining date is 2019-01-01，and terminated on 2019-11-18.
The current pay run cycle is 2019-11-01 to 2019-11-31.
If you want to count how many days of statutory holidays during the period from 3 months after the employee joined to the terminated date，then use the formula GetEmpSHDay (‘M’, 3).
26. Back to ADW of the specified date.
Name：SpecifiedADW
Function description: Combine the current specific pay item (holiday) according to the first day of the holiday, and back to ADW of the specified date
Method：GetEmpShDay（）
Parameter description: no parameter.
Eg：
var a = SpecifiedADW()
Value = a * 0.8
27．Special backpay or deduction item amount-new function
Name：VariablePayitemByEffDate
Function description: If the record of some variable pay items has been changed in the past, but it was not input in the system in time, resulting in the record being entered into the system during the current pay run cycle. This function can be calculated for the input of the historical record , what kind of amount adjustment needs to be made in the current month’s amount.
Method：VariablePayitemByEffDate (‘VariablePayItemName A’, ‘VarablePayItemValue*-+/[PayrollRecordPayItemName B]’)
Parameter description:
VariablePayItemName A: The system name of the variable pay item, and the result is the corresponding value of the variable pay item A in the supplementary record.
VarablePayItemValue：Fixed expression
[PayrollRecordPayItemName B]: The system name of the pay item. Calculation process: first find the date when the A pay item should occur and the pay run cycle that the date belongs to, and then find the corresponding value of the B pay item in the pay run cycle, and evaluate it according to the four defined calculations.
Eg1:
An employee took 3 days no pay leave in December 2019 (NoPayLeaveDays is VariablePayItemName A)
This record is input into the system in the February 2020 pay run cycle.
It is necessary to calculate the equivalent amount of no pay leave based on the average daily wage in December (ADW is PayrollRecordPayItemName B) and deduct it from the employee's salary in February. The formula can be written as:
VariablePayitemByEffDate (‘NoPayLeaveDays’, ‘VarablePayItemValue*[ADW]’)
28．Count payrpll run – new function
Name：CountPayrollRun
Function description: The current calculation belongs to how many times calculation in the payroll cycle.
Method: CountPayrollRun () result is times.
Parameter description: no parameter.
29．Find the number of days to calculate the period under the payroll policy-new function
Name：CountPayrollRun
Function description: The current calculation belongs to how many times calculation in the payroll cycle.
Method: CountPayrollRun () result is times.
Parameter description: no parameter.
30．Get expenses amount- new function
Name: GetExpensesAmount
Function description: If there is an expenses record for the employee and you want to include the expenses amount in a certain pay cycle, this formula helps.
Method: GetExpensesAmount(‘period type’,’Expenses type name’)
Parameter description:
Period type: A(attendance period) / P (payroll period)
Expenses type name:
As long as the text in parameter 2 is filled in, the expenses type will be included. If there is an expenses type name filled in that matches multiple expenses types you defined in the system, these are all included, in this case. However, If you fill in 'AllType' , it means that all expenses applications.
(Reminder: The system will automatically ignore case. For example, if you have two expenses types defined in the system named “travel expenses” and “Travel expenses”, whatever you choose to fill in from these two options, the system will take both of them when calculating.)
Eg1:
Admin in company A takes the attendance period from March 25th to April 24th to calculate employees’ salary in the pay cycle of April, making sure there is extra time for him to double check if there is anything going wrong with payroll.
In this case, if David applied for travel expenses at March 27th and admin want to include that to his salary in the pay cycle of April, the formula is written as GetExpensesAmount('A','travel expenses')
Eg2:
On the other hand, payroll period and attendance period are identical in the B company. If Laura applied for several expenses claims at March 27th and admin wants to include these to her salary in the next regular pay cycle, the formula is written as GetExpensesAmount('B','AllType')
31.Converts an expression or variable to a numeric type
Function description : Converts an expression or variable to a numeric type
Methods: ConvertToNumber (expression)
Parameter description:
Expression: a function expression or text variableExample:
1. If the basic salary of an employee is 10000, then convertToNumber (GetEmployeeInfo(' BasicPay ')) is called to return the numeric type 10000, which can be used for subsequent formula calculation
2, directly use convertToNumber ('10000') to return the numeric type 10000, or 0 if the argument is abnormal
32.Get the name of payroll policy of the current payroll calculation period
Function description: Get the name of payroll policy of the current payroll calculation period
Methods: PayrollPolicyName ()
Parameter description: none
Example:
Assuming that the payroll policy of the current salary calculation is the Sample PayrollPolicy, the payrollPolicyName () function will return the Sample PayrollPolicy for use in the formula writing judgment
33.Statistics shift type days
Function description: Statistics shift type days
Methods: CountRosterByDay ('type')
Parameter description:
Type: When the value is Working, the number of working days within the payroll period is returned; When the value is rest, return the number of scheduled Rest days; When SH, return the number of statutory holidays on schedule; When the value is empty, the number of days on schedule is returned;Example:
CountRosterByDay('Working'); CountRosterByDay('Rest'); CountRosterByDay()
34.Returns the employee's shift hours according to the attendance period corresponding to the current payroll cycle
Function description: Returns the employee's shift hours according to the attendance period corresponding to the current payroll cycle
Method: getetrosterByTime('startTime', 'endTime')
Parameter description:
StartTime: Start time
EndTime: End time
Example:
GetrosterByTime('09:00', '17:00')
2020-07-01 Roster 09:00 -- 12:00 180 min
2020-07-02 Roster 14:00 -- 18:00 180 min
2020-07-03 Roster 17:00 -- 20:00 0 min
A total of 360 min was returned
35.According to the attendance period corresponding to the current salary period, return the shift scheduling time of the designated place
Function description: According to the attendance period corresponding to the current salary period, return the shift scheduling time of the designated place
Method: getetrosterByLocation('location')
Parameter description:
Location: The name of the office punch point. If more than one location field exists, it is separated by a comma within the argumentExample:
GetrosterByLocation(' Shanghai, Shenzhen, Changsha ')
2020-07-01—2020-07-01 Roster 09:00 —12:00 location：Shanghai
2020-07-02—2020-07-02 Roster 14:00 —18:00 location：Shenzhen
2020-07-03—2020-07-03 Roster 17:00 —20:00 location：Changsha
A total of 420 min is returned
36.According to the attendance period corresponding to the current salary cycle, return the scheduling time of the specified scheduling project
Function description: According to the attendance period corresponding to the current salary cycle, return the scheduling time of the specified scheduling project
Method: GetRosterByProject('project')
Parameter description:
Project: Schedulate the name of the project, if there are more than one project field, separated by a comma within the argumentExample:
GetRosterByProject('Dance, Bike, Fight')
2020-07-01 — 2020-07-01 Roster 09:00 — 12:00 Location: Dance
2020-07-02 — 2020-07-02 Roster 14:00 — 18:00 Location: Bike
2020-07-03 — 2020-07-03 Roster 17:00 — 20:00 Location: Fight
A total of 180 min was returned
37.Evaluates whether an expression is null
Function description: Evaluates whether an expression is null. For example, var variable; isNull(variable) result is true；var variable = 10; isNull(variable) result is false
38.To a pay project specified time period, calculate the specified period specified times of pay
Function description: to a pay project specified time period, calculate the specified period specified times of pay
Method: PayItemMthlySome('PayItem', MonthDiffNumber, Index)
Parameter description:
payItem: Gets the salary item in the system. Only formula alias can be entered
MonthdiffNumber: Based on the month of the current calculation period, take the data of the previous month, for example, when the value is 1, take the salary data of the last month; when the value is 2, the current salary calculation period is 2020-05, then take the salary calculation data of 2020-03
Index: Position the period parameter after the target month. If 0, take the sum of the whole monthExample:
The current salary calculation cycle is the third salary calculation in June
The BasicSalary of an employee for the first time in March (called as base salary in the system) is 10000 respectively
The BasicSalary for the first time in April (called as BasicSalary in the system) is 15000 respectively
The BasicSalary for the first time in May (called as base salary in the system) is 20000 respectively
The first BasicSalary in June (known as base salary in the system) is 25,000 respectively
The BasicSalary of the second time in June (called as base salary in the system) is 30,000 respectively
Calculate the employee's first base salary in the month prior to the current pay cycle:
PayItemMthlySome('BasicSalary', 1, 1), the result is 20000
Calculate the employee's first base salary in the current pay cycle:
PayItemMthlySome('BasicSalary', 0, 1), the result is 25000
Calculate all base salaries of the employee in the current pay cycle:
PayItemMthlySome('BasicSalary', 0, 0), the result is 25000 + 30000 = 55000
39.Obtain the total minutes of attendance data (late/early leave/total minutes, working hours, etc.)
Function description: Obtain the total minutes of attendance data (late/early leave/total minutes, working hours, etc.)
Methods: SumAttendData('attendItemName')
Parameter description:
attendItemName: beLateLength, leaveEarlyLength, shiftLabor, totalWorkingDaysOrHours, workingOvertimeOnWeekdays, WorkingOvertimeOnRestDays, workingOvertimeOnStatutoryHolidays,absenceLength, field statistics respectively for being late, leave early, absenteeism, total attendance duration, workday overtime hours, rest day overtime hours, statutory holiday overtime hours, absent length to the total number of minutesExample:
Assume that the current salary calculation period is 2020-03-01 to 2020-03-31, and the attendance period is consistent
An employee has a record of being late on 3 days in the same month. Five minutes, eight minutes, ten minutes late
SumattendData('beLateLength') results in 23 minutes (5+8+10)
40.Result in the additional value of the drop-down option in the data dictionary according to the drop-down value information in the employee profile
Function description: result in the additional value of the drop-down option in the data dictionary according to the drop-down value information in the employee profile
Method: GetDataDictionary ('DictionaryField',Number)
Parameter Description:
DictionaryField data dictionary sub-segment name (employeeType, jobLevel, post, salaryScale)
Number: 1 or 2 or 3 or 4 or 5Example:
GetDataDictionary ('employeeType',Number)
Example detailed description:
-Assuming data dictionary personnel classification: additional value 1 is 200; additional value 1 is 300; additional value 3 is 400
-GetDataDictionary ('employeeType',1) results in 300
41.Result in the value of an employee's pay grade related to the personnel classification
Function description: Result in the value of an employee's pay grade related to the personnel classification
Method: GetTableValue (Number)
Parameter Description:
Number: 1 or 2 or 3 or 4 or 5 or 6Example detailed description:
- Assuming pay grade setting:
-Personnel classification; Salary Grade; Basic Value (1), Lowest value(2), 25th percentile(3), 50th percentile(4), 75th percentile(5), Highest value(6)
-Management; 1; 800, 200, 400, 500, 700, 900
-Management; 2; 1600, 800, 1000, 2000, 3000, 4000
-GetTableValue (3) The result is the value corresponding to the 25th percentile
42.Obtain attendance data
Function description: Obtain the number of times in line with the declaration time range of parameter 2 in attendance related data (late/early leave/confirmation, working hours, etc.), and get the number through the expression operation of parameter 3, as the operation result of the function
Method: CountAttendDatabyDay('attenditemName', 'MinsRange', 'expression')
Parameter description:
AttendItemName: beLateLength, leaveEarlyLength, shiftLabor, totalWorkingDaysOrHours,
workingOvertimeOnWeekdays, workingOvertimeOnStatutoryHolidays, workingOvertimeOnRestDays, absenceLengthMinsrange: Minute time range
expression: supports +-*/ expression
Example:
Assume that the current salary calculation period is 2020-03-01 to 2020-03-31, and the attendance period is consistent
An employee had a record of being late for work on a total of 5 days in that month. Two of them were less than 10 minutes late. The other three days were 20 minutes late
CountAttendDatabyDay('beLateLength','0-10','a*10') results in 20
CountAttendDatabyDay('beLateLength','11-20','a*50'
Compatible writing:
var str = 'tempValue' + '+' + BasicSalary + '>=0' + '&&' + 'tempValue'+ '+' + BasicSalary + '<=200000'
CountAttendDataByDay('beLateLength',str,'a*50')
Compatible writing:
var str = 'totalWorkingDaysOrHours' + '+' + 'leaveEarlyLength' + BasicSalary + '>=0' + '&&' + 'totalWorkingDaysOrHours'+ '+' + BasicSalary + '<=200000' CountAttendDataByDay('str','a*10')