The attendance sheet is the page salaries are calculated from: days present, absences, lateness and overtime. This is a ready template you can download and use in Excel, with what each column is for and the formulas that fill it in.
Download the attendance sheet template (Excel / CSV)
The file is a CSV that opens directly in Excel and Google Sheets. It holds a sample week for one employee showing each case: present, late, absent, on leave, overtime, and the weekly rest day.
Daily sheet columns
| Column | What goes in it |
|---|---|
| Employee number, name and department | To group the sheet by employee and department |
| Date and day | One day per row |
| Shift start and end | The shift that applied to the employee on that particular day |
| Check-in and check-out | From the fingerprint device or the attendance log |
| Hours worked | The difference between check-out and check-in |
| Lateness (minutes) | Arriving after the shift starts |
| Early leave (minutes) | Leaving before the shift ends |
| Approved overtime (hours) | Overtime that was actually approved, not every minute after hours |
| Status | Present, absent, leave, public holiday, weekly rest |
| Notes | The approved excuse, leave type, request number |
Formulas
In the template, shift start is column F, shift end G, check-in H and check-out I. The formulas are for row 2; drag them down to the other rows:
| Column | Formula |
|---|---|
| Hours worked (J) | =IF(OR(H2="",I2=""),0,ROUND(MOD(I2-H2,1)*24,2)) |
| Lateness in minutes (K) | =IF(H2="",0,MAX(0,ROUND((H2-F2)*1440,0))) |
| Early leave in minutes (L) | =IF(I2="",0,MAX(0,ROUND((G2-I2)*1440,0))) |
MOD(…,1)in hours worked keeps the formula right for night shifts that end after midnight.1440is the number of minutes in a day, because Excel stores a time as a fraction of a day.- If you allow a grace period, such as 10 minutes, write the rule into your attendance policy first, then adjust the lateness formula to match it.
Monthly summary per employee
On a second sheet, one row per employee:
| Item | Formula (status in column N, employee number in A) |
|---|---|
| Days present | =COUNTIFS(A:A,1001,N:N,"Present") |
| Days absent | =COUNTIFS(A:A,1001,N:N,"Absent") |
| Days on leave | =COUNTIFS(A:A,1001,N:N,"Leave") |
| Total lateness in minutes | =SUMIFS(K:K,A:A,1001) |
| Total approved overtime | =SUMIFS(M:M,A:A,1001) |
These are the figures the accountant needs to prepare the monthly payroll.
Common mistakes in a manual sheet
- One fixed shift for everyone on the sheet, although some employees work other shifts, so lateness shows where there was none.
- Leaving the status blank on leave days and holidays, so they count as absence at month end.
- Counting every minute after hours as overtime, although only approved overtime is due.
- Copying punches by hand from the device report into Excel, which is where most errors happen.
- Editing the sheet after it was approved with no trace of who changed what and why.
When does a manual sheet become a burden?
The template suits a small company with one branch and one shift. With several branches, night shifts, rosters or more than twenty employees, moving and correcting punches every month becomes a job in itself. At that point attendance management software that reads punches straight from the devices and applies the right shift to each day saves time and errors.
In the IdaraSuite attendance system this sheet builds itself from the device punches, with shifts, leave, excuses and approved overtime, and the monthly attendance report comes out ready for the accountant.
This article is general information, not legal advice. Laws and regulations change; refer to the official text in force or to a legal adviser before making a decision.