Attendance Sheet

About this template

When to use it

One person’s attendance for one month, totalled by week and by month. Three sheets — Work log, Weekly & monthly summary, How to fill in — and the first is titled “Attendance Sheet — June 2026”. Enter clock in, clock out and break, and work hours and overtime calculate themselves: this is a time sheet, not a calendar board.

Filling order

1. Rows 6–35 are 1–30 June. Change the date in column C and column D fills itself with =IF(C6=””,””,CHOOSE(WEEKDAY(C6,2),”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”,”Sun”)). Never type over column D.
2. Column E takes one of six exact strings — Regular work, Annual leave, Half-day leave, Remote work, Business trip, Holiday work — because the summary sheet counts those strings. Days off are marked Day off in the sample and stay out of the counts.
3. Enter clock in (F), clock out (G) and break in hours (H; 0.5 for 30 minutes). Columns I and J are formulas — leave them alone.
4. Late minutes go in column K, the reason in column L.
5. On the summary sheet only two cells are yours: the 168-hour monthly standard (C15) and the 21 standard workdays (C21).

How the formulas calculate

• Work hours =IF(OR(F6=””,G6=””),””,ROUND((G6-F6)*24-N(H6),1)) — clock out minus clock in, less the break.
• Overtime =IF(I6=””,””,ROUND(MAX(0,I6-8),1)) — anything past eight hours a day.
• Row 36 totals with =SUM(I6:I35), =SUM(J6:J35) and =COUNT(I6:I35)&” days worked” — the last prints the day count followed by the words days worked.
• Weekly rows use =SUM(‘Work log’!I6:I12) and flag the limit with =IF(E7>12,”Over limit”,”Within limit”) against a 12-hour weekly overtime cap, so the cell reads Over limit or Within limit.
• The category counts are =COUNTIF(‘Work log’!$E$6:$E$35,”Regular work”) — the wording is baked into the formula, so rename a label and you must edit both B26:B31 and the criteria.

Know this first

Rule 1 on the How to fill in sheet says “work hours (column H) and overtime (column I)”, but work hours are actually in column I and overtime in column J; H is the break. Typing into H because of that note corrupts the break value. Also, the “Monthly trend (reference)” table (22, 21, 20, 22 for Mar to Jun) is hand-typed reference data, not a formula, and will not move when you fill this month.

Examples

• 3 June · Regular work · 09:12 · 18:00 · break 1 → 7.8 hours, no overtime, 12 minutes late.
• 10 June · Regular work · 09:00 · 21:00 · break 1 → 11.0 hours, 3.0 overtime.
• 4 June · Annual leave — leave clock in and clock out blank; the work-hours cell stays empty and the leave count rises by one.

Common mistakes

• Writing two spellings of the same category so COUNTIF misses it.
• Typing a date as text, which breaks the weekday formula.
• Writing “1 hour” in the break column instead of the number 1.
• Reusing the file for a 31-day month without extending row 36 and the weekly ranges.