A care home · Hong Kong
Roster Excel and signed attendance to night allowance and holiday tracking
No clock: roster Excel and signed attendance drive night allowance, substitute-holiday tracking and average-daily-wage holiday pay.
Representative example — a composite of typical Hong Kong workflows, written from the duties in current job ads. Company names, documents and figures are sample data.
A care home has 58 staff on a 24-hour, 3-shift rota. Payroll uses roster Excel and a signed attendance sheet, not clock-in data. Working a statutory holiday must leave a trace in the holiday register as well as the pay calculation.
At a glance
The files they already use
- Roster Excel
- Signed attendance sheet
- Confirm actual shifts against signed attendance
- Track statutory holiday work and the substitute day
- Holiday pay uses preceding 12-month average daily wages
Done automatically
- Payroll review and holiday register
- Exceptions for review
The care-home manager confirms shifts and substitute holidays; payroll staff approve the pay.
How they used to do it
The office compares the roster with the signed attendance sheet to confirm actual shifts. Night allowance is counted from the confirmed record, while holiday work and the substitute day are kept in another register.
When those records are separated, a wage can be correct while the substitute holiday remains untracked. Holiday pay also needs its own wage-history basis rather than a convenient monthly-pay shortcut.
The difficulties
- Confirm actual shifts The roster and signed sheet are checked in separate files.
- Count night allowance Night shifts are counted again for the allowance calculation.
- Track holiday work Holiday attendance can be recorded without its substitute day.
- Show the holiday-pay basis The wage-history basis has to be found separately.
- Approve pay and leave records Payroll and the leave register can disagree without a shared review.
Their formula, broken down
Care worker Lee has base pay of HK$18,500. Night allowance is included in pay; the holiday-pay example is shown separately and is not an extra addition to this sample payslip.
Calculate night allowance
Use confirmed nights and the care home's allowance.
9 × HK$120HK$1,080
Combine gross pay
Base pay and night allowance remain separate lines.
HK$18,500 + HK$1,080HK$19,580
Deduct MPF
Employee MPF is HK$979.
HK$19,580 − HK$979HK$18,601
Show holiday pay separately
The example uses preceding 12-month wages of HK$228,000 over 365 days. A substitute holiday is scheduled and tracked for the statutory holiday worked.
HK$228,000 ÷ 365 daysHK$624.66 a day
What the system produces
Source records
| Source | Record |
|---|---|
| Base pay | HK$18,500 |
| Confirmed night shifts | 9 nights × HK$120 |
| Statutory holiday worked | Substitute holiday scheduled |
Payroll review and holiday register
| Item | Result / basis |
|---|---|
| Night allowance | HK$1,080 |
| Gross / employee MPF | HK$19,580 / HK$979 |
| Separate holiday-pay example | HK$228,000 ÷ 365 days = HK$624.66 a day |
| Net pay | HK$18,601 |
How it was calculated
Pay = base + night allowance − employee MPF; holiday pay uses its own average-daily-wage basis
HK$18,601
Flagged for a person to confirm
- Unsigned attendance or an unconfirmed substitute holiday is listed for the manager; the system does not approve the leave arrangement.
In this example: The output retains the source records and the rule behind each item. The care-home manager confirms shifts and substitute holidays; payroll staff approve the pay.
How we helped
We connect the existing source files to a review sheet, with the company's rules recorded beside the results. The care-home manager confirms shifts and substitute holidays; payroll staff approve the pay.
- Confirm actual shifts Roster entries are matched to signed attendance before they enter payroll.
- Count night allowance Confirmed night shifts feed a visible allowance line on the payroll review.
- Track holiday work Statutory holiday work and the scheduled substitute holiday remain linked for review.
- Show the holiday-pay basis The holiday-pay calculation retains the preceding 12-month wage basis beside the result.
What the system handles
Confirm actual shifts
Roster entries are matched to signed attendance before they enter payroll.
Count night allowance
Confirmed night shifts feed a visible allowance line on the payroll review.
Track holiday work
Statutory holiday work and the scheduled substitute holiday remain linked for review.
Show the holiday-pay basis
The holiday-pay calculation retains the preceding 12-month wage basis beside the result.
Approve pay and leave records
The manager reviews shift exceptions and holiday records alongside the pay sheet.
Before and after
| Topic | Before | After |
|---|---|---|
| Confirm actual shifts | The roster and signed sheet are checked in separate files. | Roster entries are matched to signed attendance before they enter payroll. |
| Count night allowance | Night shifts are counted again for the allowance calculation. | Confirmed night shifts feed a visible allowance line on the payroll review. |
| Track holiday work | Holiday attendance can be recorded without its substitute day. | Statutory holiday work and the scheduled substitute holiday remain linked for review. |
| Show the holiday-pay basis | The wage-history basis has to be found separately. | The holiday-pay calculation retains the preceding 12-month wage basis beside the result. |
| Approve pay and leave records | Payroll and the leave register can disagree without a shared review. | The manager reviews shift exceptions and holiday records alongside the pay sheet. |
How much time it saves
- Existing work: 58 × 3 h + 20 h holiday/leave register = 194 h.
- Review: 58 × 24 min + 3 h = 26.2 h.
- Estimated time saved: 194 − 26.2 = 167.8 h a month.
- The estimate values office time at HK$110 an hour (about HK$19,000 a month for a clerk, MPF included); it is not an audited cash return.
- 167.8 h × HK$110 = HK$18,458, about HK$18,500 a month.
These are the working assumptions behind the figure, not an audited result.
How a project like this runs
- Read the actual source files. We review the existing inputs with the staff who prepare them and identify where each field comes from.
- Record the rules. We turn the agreed rules into visible checks, using completed records to compare the output with the office's own calculation.
- Run alongside the existing process. The office keeps its normal process while the system handles the same files. Staff compare the results and resolve differences before switching.
- Use and maintain it. Once the results agree, staff use the review sheet and handle its flags. Changes to source formats or company rules are checked before they are applied.
Questions owners usually ask
- Does the roster alone prove attendance?
No. In this workflow, the roster is checked against the signed attendance sheet. - Who arranges the substitute holiday?
The employer arranges it; the system records the arrangement and lists anything awaiting confirmation. - Is the holiday-pay example added to this net pay?
No. It demonstrates the average-daily-wage basis separately; the sample net pay is HK$18,601.
The results
- Roster entries are matched to signed attendance before they enter payroll.
- Confirmed night shifts feed a visible allowance line on the payroll review.
- Statutory holiday work and the scheduled substitute holiday remain linked for review.
Representative example — a composite of typical Hong Kong workflows, written from the duties in current job ads. Company names, documents and figures are sample data.
Related case studies
Also useful: Back-office outsourcing · Try a working demo · Free tools · All case studies
Does your company have a formula like this?
Send us one real example — a quotation, an invoice or a month-end statement — and we will show you how the system would work it out. The assessment is free.
Get a free assessment →