Excel Project · Intermediate · 3 to 4 hours · VIP
Compute lates, overtime hours, and OT pay from a biometric log
Turn a raw time-in/time-out export into the monthly attendance summary HR actually asks for.
The brief
You support a 25-agent BPO team in Ortigas. HR sends you June's biometric export and needs the monthly attendance summary before payroll cutoff: hours worked, who was late and how often, overtime hours, and overtime pay at the standard 125 percent hourly rate. The shift starts at 9:00 AM, the schedule includes a one-hour unpaid break, and anything beyond 8 worked hours counts as overtime.
Your role
You are the team's workforce analyst. Deliverable: a per-employee summary sheet that payroll can use as-is, plus callouts for chronic lateness.
The dataset
June 2026 biometric log for 25 employees (weekdays)
attendance-log.csv · 531 rows
Columns: employee_id, employee_name, date, time_in, time_out, hourly_rate
Setup
- Download attendance-log.csv and open it in Excel or Google Sheets.
- Add helper columns for worked hours, late flag, OT hours, and OT pay before you pivot.
VIP project
The full brief for this Excel project is for VIP members
The first Excel project is free for every member. The dataset, the tasks, the answer key, and the graded skills check for this one are part of the VIP tier, along with the other 8 locked projects. 5 projects stay free, one for each tool, so you can try every tool before you decide.
VIP access comes with the bootcamp. Enroll once, and every locked project opens, alongside the live sessions, the replays, and the certificate.
Work like an AI-powered analyst
The modern analyst uses AI as a thinking partner, not a shortcut that skips the learning. Try these on this project.
- Ask ChatGPT or Claude for the cleanest formula to subtract times that may cross into the evening, then test it against edge rows.
- Describe the Philippine 125 percent OT rule to the AI and ask it to review whether your formula applies it correctly.
- Ask the AI to suggest 3 more insights HR would appreciate from this exact dataset, and build one of them.
Finished it? Put it in your portfolio.
This is exactly the kind of output the bootcamp builds with you live, with mentor feedback and an AI badge and certificate of completion at the end.