Excel Project · Intermediate · 3 to 4 hours
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.
Your tasks
Work through these in order, the way the engagement would actually run.
- 1Compute worked hours per row: time_out minus time_in, minus the 1-hour unpaid break.
- 2Flag lates: any time_in after 9:00 AM.
- 3Compute OT hours per row: worked hours beyond 8, never negative.
- 4Compute OT pay per row: OT hours times hourly_rate times 1.25.
- 5Pivot per employee: days present, late count, total OT hours, total OT pay.
- 6Apply conditional formatting to highlight employees with 8 or more lates.
- 7Rank the top 3 OT earners and sanity-check their rows manually.
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.
Expected output
- A working sheet with four correct helper columns: worked hours, late flag, OT hours, OT pay.
- A per-employee pivot summary ready for payroll, sorted by OT pay.
- A highlighted list of chronic latecomers with their late counts.
Check your numbers
Your results should match these. If they do not, that is the real learning: find out why.
- The log has 531 attendance rows (absences are simply missing rows).
- Total late instances across the team in June: 133.
- Total overtime is approximately 286 hours, and total OT pay is approximately ₱42,968.
- The top OT earner is Paolo Garcia at approximately ₱4,281.
- The most frequently late employee is Ivy Gonzales with 12 lates.
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.