Free Excel Attendance Tracker Template — Clock-In/Out Records with Auto Summary
Record employee clock-in and clock-out times and automatically calculate work hours, overtime, tardiness, and absences. Ideal for small teams.
Small teams often manage attendance with spreadsheets rather than expensive HR software. This template turns that spreadsheet into a smart attendance system — enter clock-in and clock-out times, and everything else is calculated automatically.
Key Features
📋 Monthly Attendance Sheets
- Enter employee name, clock-in time, and clock-out time for each day
- Work hours auto-calculated — subtracts lunch break automatically
- Late arrivals (after designated start time) flagged automatically
- Early departures (before designated end time) flagged automatically
- Weekends and holidays shaded in gray
📊 Monthly Summary Report
| Metric | How It Works |
|---|---|
| Total Work Hours | Sum of daily hours per employee |
| Overtime Hours | Hours beyond standard work hours |
| Late Count | Number of days arrived after start time |
| Absence Count | Working days with no clock-in record |
| Early Leave Count | Days departed before end time |
Employees with overtime are highlighted in blue; those with 3+ late arrivals are highlighted in red.
⚙️ Settings Sheet
- Set standard start time and end time (e.g., 9:00 AM / 6:00 PM)
- Configure lunch break duration (default: 1 hour)
- Register employee roster (up to 30 employees)
How to Use
Step 1: Register Employees
Enter employee names in the “Settings” sheet. Names will appear in the dropdown menus on the monthly sheets.
Step 2: Record Clock-In/Out Times
On the current month’s sheet, enter the clock-in time (e.g., 9:05) and clock-out time (e.g., 18:30) for each day. Leave absent days blank — they are flagged automatically.
Step 3: Review the Summary
Check the “Summary” sheet for each employee’s monthly attendance statistics. Print-optimized layout included.
Tips
Handling Half-Days
For half-day leave, enter the actual times (e.g., clock-in at 13:00 for afternoon-only). Add “Half-day” in the notes column for easy filtering later.
Tracking Remote Work
Enter “Remote” in the notes column for work-from-home days. Use COUNTIF at month-end to quickly tally remote work days per employee.
FAQ
What if we have more than 30 employees?
Expand the employee list in the Settings sheet and extend the formula ranges in the Summary sheet accordingly.
How do I handle overnight shifts?
For clock-out times past midnight, add 1 to the time value. For example, a 2:00 AM departure is entered as =TIME(2,0,0)+1.
Can multiple people edit the file at the same time?
Convert to Google Sheets for real-time collaboration. Some formulas may need minor adjustments after conversion.