Employee Attendance Sheet in Excel with Formula: Monthly & Yearly Tracker

Employee Attendance Sheet is an automated attendance tracker in Excel with formulas already built. Add your people once on the Employees tab and they appear on all 12 monthly attendance sheets. Type one code per person per day, or pick it from the dropdown, and each month works out totals, attendance %, late and early counts and payable days. There is nothing to copy down and no formula to write, which makes it a practical attendance sheet format for any company, office or staff team.

Set the year once and every month sheet updates its dates and weekdays. Weekly off days are editable, so Saturday and Sunday can become Friday and Saturday or a six-day week, and the holidays you list are shaded on every sheet and left out of working days. Twelve attendance codes are included, such as P present, A absent, L annual leave, SL sick leave, HD half day, WFH work from home, LT late and EL left early. You can rename them and set how much each one counts as worked and as paid, so a half day counts 0.5. Working days still waiting for a code are highlighted in amber, and employees who join or leave mid-year are handled by their dates.

The Yearly Summary gives a 12-month attendance register for each employee, with code totals, days worked, annual and sick leave balances, late days, overtime hours, payable days, a status alert and a monthly attendance heatmap. The Dashboard lets you pick a month to see attendance, absent days, late arrivals and leave, along with the year at a glance, a department comparison, people present per day and a list of who needs a conversation.

The Daily Sheet is a printable daily attendance sheet: pick a date to see who was present, absent or on leave, with time in and time out. The Employee Card shows one person's full year on a single page for the HR file or a review. The optional Time Log records time in and time out for each shift, works out late minutes, early minutes, hours and overtime, handles night shifts that cross midnight, and flags any entry that disagrees with the month sheet for review.

Monthly payable days are ready to pass to payroll. They add each code's paid value, and can include paid weekly offs and holidays with one setting. Capacity covers 100 employees, 12 attendance codes, 8 shifts, 40 holidays and 3,000 time log entries. Calculated cells are locked and sheet protection has no password. Sample data from a fictional company is included. Built for Microsoft Excel 2016 or later (desktop or Excel for the web) and contains no macros.