Payroll Functions Calculation-Built-in Functions Tutorial

Don’t know how to add a new pay item formula? Here to find what you need ,you can apply immediately!

Workstem CS avatar
Written by Workstem CS
Updated over a week ago

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 variable

  • Example:
    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 argument

  • Example:
    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 argument

  • Example:
    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 month

  • Example:
    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 minutes

  • Example:
    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 5

  • Example:
    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 6

  • Example 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, absenceLength

    • Minsrange: 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')

Did this answer your question?