About this skill

Problem Solved

After exporting WeCom attendance reports, HR staff often have to reconcile leave and outing approvals manually, then calculate late arrivals, early departments, rest days, and holidays by morning and afternoon. The raw Excel file usually contains only attendance results, not approval status, cross-day outing boundaries, or month calendar rules, so adjusted workdays, cross-day outings, and simplified/traditional name variants can be easy to miscount.

How It Works

The skill reads attendance exports, leave requests, and outing approvals from the current working directory, then performs annotation and statistical analysis:
- Annotation: filters valid leave and outing records by “approved and covering the attendance month,” writes leave in red and outings in black into report cells, and outputs {year}{month}Attendance Report_Annotated.xlsx.
- Statistics: splits each employee day into AM/PM, colors rest, late arrival, early departure, and leave, and includes 2026 statutory holidays with adjusted workday handling, outputting {year}{month}Attendance Statistics.xlsx.
- File matching: finds files by name patterns such as attendance report and annotated, supports arbitrary month length via calendar.monthrange, and evaluates cross-day outings by start, middle, and end days.

Boundaries

It is intended for monthly attendance folders that follow the WeCom export structure and have write-capable Excel handling with openpyxl. Input filenames should follow the naming convention. The holiday dictionary is currently built for 2026 only; other years require manual updates. Name matching handles simplified/traditional and variant characters, but it does not guarantee recognition of all aliases or names outside the system.

Screenshots

Use Cases

After month-end WeCom exports, annotate approved leave and outing approvals into the raw report.
Generate a color-coded AM/PM sheet for late arrivals, early departures, leave, and rest days.
Handle cross-day outings and adjusted 2026 holiday workdays by splitting start, middle, and end days.
Auto-match simplified and traditional name variants to reduce manual approval-to-attendance checks.

Best For

HR staff handling monthly WeCom attendance: annotate approvals and produce statistical results.
Payroll or attendance clerks: need AM/PM breakdowns of lateness, early departure, leave, and rest.
Automation engineers: want reusable file matching, holiday, and cross-day outing rules.
Admin leads managing departments: reduce manual checks for name variants and approval records.