Formulas are where a company brings its own pay policy into the system. They are written like Excel, with one important difference: you use variable names instead of cell addresses, so formulas are readable and don't break when columns are added.
How to write them
A formula starts with the `=` sign, followed by keywords combined with arithmetic operators. An example included in the template:
| Column | Formula |
|---|---|
| Total allowances | `= PHUCAP_TINH_THUE + PHUCAP_KHONG_THUE` |
Always use keywords, not column letters. The letters A, B, C are only for locating columns during review; if you insert a column, the letters shift, but keywords don't change.
Variable library
The panel on the right of the Column Configuration screen lists every available variable, grouped by category, with a Find column search box. Variables already used in the template are shown in gray.
| Variable group | Contains |
|---|---|
| Basic information | ID, name, position, department, bank account |
| Time items | The period's milestones and time ranges |
| Overtime & extra hours within shift | Overtime hours by type |
| Shifts | Number of shifts, shift type |
| Branch | Workplace information |
| Leave, holidays & business trips | Days off by type |
| Additions | Allowances and bonuses, split into subject to social insurance and not subject to social insurance |
| Deductions | Salary advances, fines, other deductions |
| Personal income tax | Taxable income and tax payable |
| Salary | Base salary levels |
| Requests | Data taken from approved requests |
| Assets | Asset-related deductions |
| KPI constants | Coefficients used for performance-based pay |
Three principles for writing robust formulas
- Split into multiple intermediate columns instead of one long formula. One Total allowances column, one Total deductions column, then a Net pay column — much easier to review than a ten-line expression.
- Name columns by business meaning, not technical terms. The people who check the payroll are accountants, not programmers.
- Don't embed hard-coded numbers in formulas. Rates, coefficients, and thresholds should be set up as items in Salary Grades or as separate columns, so you only need to edit one place when they change.
The third principle matters most for legal figures. Writing a rate directly into the middle of a formula means that when regulations change, you'll have to hunt for it in every payroll template for every branch — and you will miss some.
Test before using for real
- Click View in the right corner to review the template.
- Create a test period with one small department.
- Select three to five employees in different situations: full attendance, on leave, with overtime, with a salary advance.
- Calculate each person by hand and compare with the system's results.
- Wherever there's a discrepancy, trace it back following article B9-18.
Formula errors shown on the payroll
| Symptom | Common cause |
|---|---|
| Cell shows `#NAME?` | The formula references a keyword that doesn't exist — usually a mistyped variable name or an item that was deleted |
| Cell is blank while other rows have figures | That employee is missing source data, for example no salary amount entered |
| Result is 0 | A variable in the formula is 0, or the employee has no pay configuration |
The `#NAME?` error cell appears directly on the employee's payslip if it isn't resolved before publishing. Always review all error cells before clicking Approve & lock.
