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.

167.8 hoursestimated time saved a month
TargetTraceablerules shown with the output
TargetReviewedexceptions reach a person

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
AI systembuilt around their rules
  • 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.

Their formulaPay = base + night allowance − employee MPF; holiday pay uses its own average-daily-wage basis
  1. Calculate night allowance

    Use confirmed nights and the care home's allowance.

    9 × HK$120HK$1,080

  2. Combine gross pay

    Base pay and night allowance remain separate lines.

    HK$18,500 + HK$1,080HK$19,580

  3. Deduct MPF

    Employee MPF is HK$979.

    HK$19,580 − HK$979HK$18,601

  4. 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

Net pay to LeeHK$18,601

What the system produces

sample.alveek.ai/payroll-care-homeSample data

Source records

Roster ExcelSigned attendance sheet
SourceRecord
Base payHK$18,500
Confirmed night shifts9 nights × HK$120
Statutory holiday workedSubstitute holiday scheduled

Payroll review and holiday register

Review sheet (Excel)Output for approval (PDF)
ItemResult / basis
Night allowanceHK$1,080
Gross / employee MPFHK$19,580 / HK$979
Separate holiday-pay exampleHK$228,000 ÷ 365 days = HK$624.66 a day
Net payHK$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.

  1. Confirm actual shifts Roster entries are matched to signed attendance before they enter payroll.
  2. Count night allowance Confirmed night shifts feed a visible allowance line on the payroll review.
  3. Track holiday work Statutory holiday work and the scheduled substitute holiday remain linked for review.
  4. 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

TopicBeforeAfter
Confirm actual shiftsThe roster and signed sheet are checked in separate files.Roster entries are matched to signed attendance before they enter payroll.
Count night allowanceNight shifts are counted again for the allowance calculation.Confirmed night shifts feed a visible allowance line on the payroll review.
Track holiday workHoliday attendance can be recorded without its substitute day.Statutory holiday work and the scheduled substitute holiday remain linked for review.
Show the holiday-pay basisThe 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 recordsPayroll 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

  1. Read the actual source files. We review the existing inputs with the staff who prepare them and identify where each field comes from.
  2. Record the rules. We turn the agreed rules into visible checks, using completed records to compare the output with the office's own calculation.
  3. 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.
  4. 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.
PythonClaudeExcelWhatsAppEmail

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.

← Back to 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 →