Chapter 19

Case Study 2 — HR Personnel Data Management System

Chapter 19: HR System — Attendance, Payroll, and Performance Automation

HR work has three core time sinks: attendance aggregation at month start, payroll calculation mid-month, and performance reviews each quarter. All three are high-volume, high-accuracy, low-error-tolerance. This chapter delivers complete Excel+AI solutions across 20 real-world scenarios.

HR Excel Pain Points

Attendance: Raw data from time-tracking systems exports in messy formats requiring extensive manual cleanup. Leave types, comp days, half-days add calculation complexity.
Payroll: Each employee's package is different; overtime rules are complex; tax calculations change yearly. One error triggers employee complaints and trust issues.
Performance: Multi-dimension, weighted KPI scoring is error-prone manually; employees scrutinize the results carefully — accuracy is non-negotiable.
Data management: Employee info scattered across files; onboarding/offboarding/transfer updates lag, causing errors in payroll or reports.

Attendance Automation (5 Cases)

1Attendance Table Structure Design — AI-Optimized

2Auto-Calculate Attendance / Late / Early Leave / Overtime

Core Formula Set
    Columns: C = clock-in time (HH:MM text), D = clock-out, E = date
    Late flag (after 9:10): =IF(TIMEVALUE(C2)>TIME(9,10,0),"Late","Normal")
    Late minutes: =IF(TIMEVALUE(C2)>TIME(9,10,0),(TIMEVALUE(C2)-TIME(9,10,0))*1440,0)
    Overtime hours (past 6:30 PM): =IF(TIMEVALUE(D2)>TIME(18,30,0),(TIMEVALUE(D2)-TIME(18,30,0))*24,0)
    Monthly late count: =COUNTIFS(Punches!B:B,A2,Punches!F:F,"Late",Punches!E:E,">="&DATE(YEAR(G1),MONTH(G1),1))

3Required Working Days (NETWORKDAYS Excluding Holidays)

NETWORKDAYS Application
    =NETWORKDAYS(start_date, end_date, holidays_range)
    Example: April working days (excluding public holidays in Sheet "Holidays", col A):
    =NETWORKDAYS(DATE(2026,4,1), DATE(2026,4,30), Holidays!A:A)
    Comp work days (weekends designated as working days) require special handling:
    NETWORKDAYS automatically excludes weekends, but comp work days need to be added back manually using a separate "comp_workday" list.

4Attendance Anomaly Auto-Flagging (Conditional Format)

5AI-Built Attendance Template from Scratch

Payroll Automation (5 Cases)

1Payroll Formula (Base + Performance + Allowances - Deductions)

2Income Tax Calculation (Rate Table + VLOOKUP)

Progressive tax rates require a lookup table approach. The key is building the rate table so VLOOKUP approximate match returns the correct bracket.

3Social Insurance and Housing Fund Calculation

Social Insurance Calculation Structure
    Contribution base = MAX(MIN(salary, upper_limit), lower_limit)
    (Upper and lower limits stored in a Settings table — updated annually)
    Employee portions (China reference rates, verify locally):
    Pension: base × 8%
    Medical: base × 2%
    Unemployment: base × 0.5%
    Housing fund: base × 7% (varies by company policy)
    Best practice: store all rates and limits in a "Settings" sheet.
    Annual rate changes only require updating the Settings sheet.

4Pay Slip Generation — Individual Slips via VBA

5AI Reviews Payroll Logic

Performance Management (4 Cases)

1KPI Scorecard Structure Design

2Weighted Score Calculation (SUMPRODUCT)

SUMPRODUCT for Weighted Scoring
    Weights in B1:E1 (sum to 1), scores in B2:E2:
    Weighted total = =SUMPRODUCT($B$1:$E$1, B2:E2)
    Performance grade (S/A/B/C/D):
    =IFS(F2>=90,"S",F2>=80,"A",F2>=70,"B",F2>=60,"C",TRUE,"D")

3Forced Distribution Grade Assignment (IFS + RANK)

4AI-Generated Performance Feedback

Employee Records (3 Cases)

1Employee File Design (Data Validation + Dropdowns)

2Birthday / Contract Expiry Alerts (DATEDIF + Conditional Format)

Key Formulas
    Days until birthday this year (wraps to next year if passed):
    =DATEDIF(TODAY(), DATE(YEAR(TODAY()),MONTH(D2),DAY(D2)), "D")
    Contract days remaining: =DATEDIF(TODAY(), E2, "D")
    Conditional format: contract days 3Turnover Rate / Headcount Analysis



## Recruitment Tracker (3 Cases)


  1Candidate Pipeline Management



---

---

  2Interview Schedule Sheet



---

  3Offer Letter Generation (AI-Assisted)



## Monthly HR Report Automation (Comprehensive Case)


Standard monthly HR report structure: headcount overview → recruitment progress → attendance summary → payroll cost → performance update → next month focus.


  [
    Previous
    ← Chapter 18: Sales Dashboard
  ](/books/ai-excel/ch18-sales)
  [
    Next
    Chapter 20: Finance Analysis →
  ](/books/ai-excel/ch20-finance)

Rate this chapter

4.6 / 5 (10 ratings)